database management

Place for questions and answers for all newcomers and new coders. This is a free for all forum, no question is too stupid and to noob.
Post Reply
Oroton
Posts: 39
Joined: Wed Dec 02, 2015 4:56 am

database management

Post 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.
User avatar
a_bertrand
Posts: 1537
Joined: Mon Feb 25, 2013 1:46 pm

Re: database management

Post 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 ;)
Creator of Dot World Maker
Mad programmer and annoying composer
Oroton
Posts: 39
Joined: Wed Dec 02, 2015 4:56 am

Re: database management

Post 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.
User avatar
hallsofvallhalla
Site Admin
Posts: 12031
Joined: Wed Apr 22, 2009 11:29 pm

Re: database management

Post 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.
User avatar
a_bertrand
Posts: 1537
Joined: Mon Feb 25, 2013 1:46 pm

Re: database management

Post 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.
Creator of Dot World Maker
Mad programmer and annoying composer
Post Reply

Return to “Beginner Help and Support”