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