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.

database management

Postby Oroton » Wed Aug 28, 2019 4: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.
Oroton
 
Posts: 39
Joined: Tue Dec 01, 2015 11:56 pm
Has thanked: 9 times
Been thanked: 1 time

Re: database management

Postby a_bertrand » Wed Aug 28, 2019 9:19 am

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
User avatar
a_bertrand
New Worlds Engine creator
 
Posts: 1535
Joined: Mon Feb 25, 2013 8:46 am
Has thanked: 15 times
Been thanked: 23 times

Re: database management

Postby Oroton » Thu Aug 29, 2019 8:21 pm

char_inv.PNG
char_inv.PNG (5.36 KiB) Viewed 3493 times
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
char_inv.PNG (5.36 KiB) Viewed 3493 times


i'm just not sure i want 1000*1000&*1000 rows in a table,

this is more for like msql and php.
Oroton
 
Posts: 39
Joined: Tue Dec 01, 2015 11:56 pm
Has thanked: 9 times
Been thanked: 1 time

Re: database management

Postby hallsofvallhalla » Tue Sep 03, 2019 9:16 pm

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
hallsofvallhalla
Site Admin
 
Posts: 11998
Images: 13
Joined: Wed Apr 22, 2009 6:29 pm
Location: mobile, al
Has thanked: 11 times
Been thanked: 164 times
Blog: View Blog (3)

Re: database management

Postby a_bertrand » Wed Sep 04, 2019 7:14 am

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
User avatar
a_bertrand
New Worlds Engine creator
 
Posts: 1535
Joined: Mon Feb 25, 2013 8:46 am
Has thanked: 15 times
Been thanked: 23 times


Return to Beginner Help and Support

Who is online

Users browsing this forum: No registered users and 2 guests

cron

x