Newbie question on database efficiency

Question

I am currently building an online game where data is stored in MySQL. I have three tables:

• Characters
• Items
• Inventory

They are built in this manner:

Characters

| ID(key)   | Name  | Other...  |
| 1         | Adam  | content   |
| 2         | Ben   | content   |
| 3         | Con   | content   |
| 4         | Dun   | content   |
| ...       | Eab   | content   |

Items

| ID(key)   | Name      | Other...  |
| 1         | Potion    | content   |
| 2         | Sword     | content   |
| 3         | Shield    | content   |
| 4         | Card      | content   |
| ...       | Food      | content   |

Inventory

| ID(key)   | CharacterID   | ItemID    |
| 1         | 1             | 2         |
| 2         | 4             | 2         |
| 3         | 4             | 1         |
| 4         | 2             | 3         |
| ...       | 4             | 3         |

What I am looking for is if this is efficient and an OK method to use for storing this type of data? Instead of having to store the name of the item over and over I just reference it's ID from the items table.

The PHP code creates a JOIN to find the character ID and the item ID then searches for it on the inventory table. Then I can pull in any data I want.

If this is not efficient or if I have created a problem, please point me in the right direction so i can resolve this before it becomes a large issue at launch.

Answer 1

Knowing everything you have specified, I suggest you to create this model:

/********Characters********
this is the table where all the players exist
*/
CREATE TABLE characters (
    id      <id_type> NOT NULL,
    name    <column_type> ,
    other   <column_type>  
);
ALTER TABLE characters ADD CONSTRAINT c_pk PRIMARY KEY ( id );

/********Item Type********
this is the type of item; i.e. poison, sword,...
*/
CREATE TABLE item_type (
    id      <id_type> NOT NULL,
    name    <column_type>,
    other   <column_type> 
);
ALTER TABLE item_type ADD CONSTRAINT t_pk PRIMARY KEY ( id );

/********Items********
this is every single and unique existence of any item in the game...
*/
CREATE TABLE items (
    id       <id_type> NOT NULL,
    typeid   <id_type> NOT NULL,
    other    <column_type> 
);
ALTER TABLE items ADD CONSTRAINT i_pk PRIMARY KEY ( id );
ALTER TABLE items ADD CONSTRAINT i_t_fk FOREIGN KEY ( typeid ) REFERENCES item_type ( id );

/********Inventory********
this relates all players with all of their items
*/
CREATE TABLE inventory (
    characterid   <id_type> NOT NULL,
    itemid        <id_type> NOT NULL
);
ALTER TABLE inventory ADD CONSTRAINT in_pk PRIMARY KEY ( characterid, itemid );
ALTER TABLE inventory ADD CONSTRAINT in_c_fk FOREIGN KEY ( characterid ) REFERENCES characters ( id );
ALTER TABLE inventory ADD CONSTRAINT in_i_fk FOREIGN KEY ( itemid ) REFERENCES items ( id );

I hope this solution helps you to better approach what you are looking for your game.

Answer 2

Schema Design

Right off, I don't see anything the screams "performance problem" with your table design.

You will want ensure you have correct indexes. You will need to perform benchmarks.

Databases handle millions of rows every minute. (Some, every second).

Run benchmarks to (dis)prove my statement.

Benchmark

Performance problems for newbies usually comes from how the middle tier interacts with the database tier.

If done improperly, you can easily DDoS your database with a handful of users.

You really need to run multiple benchmarks to ensure the "end game" performs to your specification.

Make sure your code is properly instrumented so that you easily spot performance problems.