Mysql – Newbie question on database efficiency

MySQL

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.

Best Answer

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.

Related Question