Page 1 of 1

database management

Posted: Wed Aug 28, 2019 9:05 am
by Oroton
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.

Re: database management

Posted: Wed Aug 28, 2019 2:19 pm
by a_bertrand
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 ;)

Re: database management

Posted: Fri Aug 30, 2019 1:21 am
by Oroton
char_inv.PNG
basically so when i attack i call on the item,

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.
char_inv.PNG
i'm just not sure i want 1000*1000&*1000 rows in a table,

this is more for like msql and php.

Re: database management

Posted: Wed Sep 04, 2019 2:16 am
by hallsofvallhalla
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.

Re: database management

Posted: Wed Sep 04, 2019 12:14 pm
by a_bertrand
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.