Jump to content

  • Log In with Google      Sign In   
  • Create Account

FREE SOFTWARE GIVEAWAY

We have 4 x Pro Licences (valued at $59 each) for 2d modular animation software Spriter to give away in this Thursday's GDNet Direct email newsletter.


Read more in this forum topic or make sure you're signed up (from the right-hand sidebar on the homepage) and read Thursday's newsletter to get in the running!


#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