So this is just a question on best practice for a large database.
Say i have table `characters` and table `items`
lets say that i have 1000 characters/users and 5000 items.
But for each character they might have say 3x 'swords' and 2 might have different stats say durability 15 and the other one might be 1
if you are getting what i'm putting down what would be the best way to manage it considering, say there is every possibility
1000 users x 1000 items to the Nth unique item.
With the 'character' table and 'items' table and id holders, should I
A. One table per character ID, 'character_inventory_id_X' and rows for each item.
B. Many tables 'char_inventory' 'char_inv_attribute' link the unique id for each item, and then link it to the attribute, with multiple rows for each qty of item (so if i have 18 swords, 18 rows)
C. a file for each character. say a unque file, char_idX.inv on the server that is writable and update it for each item. a row for every item or something. I feel this is abusable.
D. The neatest way but unsure how this would work I think would be table `char_inventory` with columns. `qty`, `unique_attribute1`, `unique_attribute2`
for this and store them like an array so it would be like
char_id, 1
item_id, 2
qty, 11,4,3
durability, 15, 12, 1
strength, 10, 12, 10
for me this seems like the least amount of queries, but then it seems i'd need a column for every atribute, and unsure how best practice would be if i wanted it to be more dynamic.
Any helps here would be great.
Thanks.
database management
- a_bertrand
- Posts: 1536
- Joined: Mon Feb 25, 2013 1:46 pm
Re: database management
All depends what you need to do with those data. If it's just for the game itself then the play_game_item table could have a column (text) which contains just a JSON with the attributes. If you need to query it, then it should be in a separated table such that you can query it. Also avoid multiple queries instead use joins
Creator of Dot World Maker
Mad programmer and annoying composer
Mad programmer and annoying composer
Re: database management
matching it to say character id. and then once i've attacked i want to change the durabiltiy from 15 to 14.
but lets say i have 10 swords with varying durabilities or different statistics. should i have one table for all items, for all characters.
a unique table for each user or character id. or one row in one column, with multiple variables in each cell that is called upon like an array.
like below.
i'm just not sure i want 1000*1000&*1000 rows in a table,
this is more for like msql and php.
- hallsofvallhalla
- Site Admin
- Posts: 12023
- Joined: Wed Apr 22, 2009 11:29 pm
Re: database management
It could get squirly trying to navigate a bunch of commas, especially when removing and adding. I am not sure why a character would have 1000 items x n, wouldn't the average character have 100 items period?
Even then 1,000 characters with 1,000 items is only 1 million rows. I currently have DBs with 100 million rows and no issues. You just need to have indexes set properly.
Even then 1,000 characters with 1,000 items is only 1 million rows. I currently have DBs with 100 million rows and no issues. You just need to have indexes set properly.
- a_bertrand
- Posts: 1536
- Joined: Mon Feb 25, 2013 1:46 pm
Re: database management
Honestly I believe your DB design is clumsy, I doubt you need to many entries per users, and certainly don't need so many queries. Well thinking make you save performances Also mysql can handle without much troubles large quantities of rows if... you have a correct DB setup.
Creator of Dot World Maker
Mad programmer and annoying composer
Mad programmer and annoying composer