1

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.

  • Join is fine unless you have millions of inventory entries. If you do not need to combine duplicate Incentors items you would also use the Inventory table in the join (and not „search in Inventory“). And you also might consider caching.. of course don’t forget Index for those columns. – eckes Jun 26 '18 at 18:38
  • There are too many variables to give you a definitive answer. You should really plan for performance and scalability testing of your application. – mustaccio Jun 26 '18 at 19:40
  • Thank you eckes and mustaccio. For referencing millions of records, is there a better approach of saving this data? This seemed to be the smallest way of handling this type of data. – Zach Voss Jun 26 '18 at 19:56
  • 1
    If you want easy guidance, migrate to PostgreSQL before it's too late. – Evan Carroll Jun 26 '18 at 22:28
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.

  • I see how this reduces the amount of data being stored in the table. I will implement this. Thank you. – Zach Voss Jun 29 '18 at 13:24
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.