Jump to content

  • Log In with Google      Sign In   
  • 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. laugh.png

 

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. laugh.png

 

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. :lol:

 

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