• Create Account

### #ActualSky Warden

Posted 25 May 2013 - 05:17 AM

because it sounds like possible overkill if you have 100,000 distinct tables.

It will. Trust me. Even in my multiplayer RPG game database design, I just need about 43 tables to cover most of the things like items, skills, magics, users, characters, quests, etc.

I think, instead of using a table for each character to contains its items, you can do something like this:

character_table : id, name, other, information, needed
item_table : id, item_name, type, price, weight, other, information, needed


And make a pivot table like this:

character_items : id, character_id, item_id, quantity, slot_number


The character_id and item_id attributes in the pivot table refer to the id of character_table and item_table.

The tuples will be like this:

id, character_id, item_id, quantity, slot_number
01, 142         , 153    , 30      , 1
02, 143         , 153    , 20      , 5
03, 142         , 192    , 14      , 2
04, 142         , 112    , 11      , 3


See? You don't need one table for each character.

Oh no! I want to delete character 142! Simple. MySQL, just delete any tuple in character_item table where the character_id is 142.

Oh no! I want to delete item 153! Simple. Just do like the previous.

If you need to use the item information for something else, like what items are in a room, you can simply make a pivot table to connect the room table with the item table. This makes your data re-usable, and, magically, reduce redundancy.

You don't need a table with 1000000 columns either. Remember that those attributes can be separated into tables. Just use your imagination.

As for empty data in a row, try to keep it minimum. It just doesn't feel right to me.

Remember to make the id attribute a numerical auto_increment-ed column and set it as primary_key. It will make the search faster. It's very good for consistency.

My Little SQL : Normalization is Magic.

### #2Sky Warden

Posted 25 May 2013 - 05:16 AM

because it sounds like possible overkill if you have 100,000 distinct tables.

It will. Trust me. Even in my multiplayer RPG game database design, I just need about 43 tables to cover most of the things like items, skills, magics, users, characters, quests, etc.

I think, instead of using a table for each character to contains its items, you can do something like this:

character_table : id, name, other, information, needed
item_table : id, item_name, type, price, weight, other, information, needed


And make a pivot table like this:

character_items : id, character_id, item_id, quantity, slot_number


The character_id and item_id attributes in the pivot table refer to the id of character_table and item_table.

The tuples will be like this:

id, character_id, item_id, quantity, slot_number
01, 142         , 153    , 30      , 1
02, 143         , 153    , 20      , 5
03, 142         , 192    , 14      , 2
04, 142         ,  112   , 11      , 3


See? You don't need one table for each character.

Oh no! I want to delete character 142! Simple. MySQL, just delete any tuple in character_item table where the character_id is 142.

Oh no! I want to delete item 153! Simple. Just do like the previous.

If you need to use the item information for something else, like what items are in a room, you can simply make a pivot table to connect the room table with the item table. This makes your data re-usable, and, magically, reduce redundancy.

You don't need a table with 1000000 columns either. Remember that those attributes can be separated into tables. Just use your imagination.

As for empty data in a row, try to keep it minimum. It just doesn't feel right to me.

Remember to make the id attribute a numerical auto_increment-ed column and set it as primary_key. It will make the search faster. It's very good for consistency.

My Little SQL : Normalization is Magic.

### #1Sky Warden

Posted 25 May 2013 - 05:16 AM

because it sounds like possible overkill if you have 100,000 distinct tables.

It will. Trust me. Even in my multiplayer RPG game database design, I just need about 43 tables to cover most of the things like items, skills, magics, users, characters, quests, etc.

I think, instead of using a table for each character to contains its items, you can do something like this:

character_table : id, name, other, information, needed
item_table : id, item_name, type, price, weight, other, information, needed


And make a pivot table like this:

character_items : id, character_id, item_id, quantity, slot_number


The character_id and item_id attributes in the pivot table refer to the id of character_table and item_table.

The tuples will be like this:

id, character_id, item_id, quantity, slot_number
01, 142         , 153    , 30      , 1
02, 143         , 153    , 20      , 5
03, 142         , 192    , 14      , 2
04, 142         ,  112   , 11      , 3


See? You don't need one table for each character.

Oh no! I want to delete character 142! Simple. MySQL, just delete any tuple in character_item table where the character_id is 142.

Oh no! I want to delete item 153! Simple. Just do like the previous.

If you need to use the item information for something else, like what items are in a room, you can simply make a pivot table to connect the room table with the item table. This makes your data re-usable, and, magically, reduce redundancy.

You don't need a table with 1000000 columns either. Remember that those attributes can be separated into tables. Just use your imagination.

As for empty data in a row, try to keep it minimum. It just doesn't feel right to me.

Remember to make the id attribute a numerical auto_increment-ed column and set it as primary_key. It will make the search faster. It's very good for consistency.

My Little SQL : Normalization is Magic.

PARTNERS