I am currently building an online game where data is stored in MySQL. I have three tables:
They are built in this manner:
| ID(key) | Name | Other... | | 1 | Adam | content | | 2 | Ben | content | | 3 | Con | content | | 4 | Dun | content | | ... | Eab | content |
| ID(key) | Name | Other... | | 1 | Potion | content | | 2 | Sword | content | | 3 | Shield | content | | 4 | Card | content | | ... | Food | content |
| 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.