MySql table design: multiple tables vs. multiple columns_问答_开发者

I'm stumped on the ideal method of dealing with a relatively large number of table fields, and whether or not they should be split off into separate relational tables.

I've got a set of tables for a web-based game of sorts, holding player data, item data, class data, etc. For each one of these (player/item/class) I also need to record a number of stats (i.e. health, spirit, armor, etc.). Currently the list is 25 stats, which means each of these tables have 50 additional columns (type/value pairs), in addition to the columns they already have.

This is starting to feel cumbersome.

The alternative is to move all stats into separate tables. For example, items stats with be in a "item stats relation" table, with IDs linking into the item and stat tables.

This would simplify the table structure and give flexibility if I ever need to change the number of stats. However, it also adds to the complexity of maintaining the data. If I add a new item, rather than just inserting a single row into the item table, I now have to insert many more rows into the "item stat relation" table to include all of the stats it needs.开发者_高级运维

Is it worth the extra effort to keep the tables simple?

Edit: Forgot to add the other alternative I was considering: serializing all stats into a VARCHAR or TEXT column. This too would give me flexibility to add any number of stats (especially if I used TEXT), and doesn't add any complexity to the code that handles it. However, from what I've read, TEXT blocks are bad for performance, and I'd have to access it fairly frequently, reading and writing the entire block every time.

The alternative is to move all stats into separate tables. For example, items stats with be in a "item stats relation" table, with IDs linking into the item and stat tables.

This is the correct method of dealing with the business rules.

If I add a new item, rather than just inserting a single row into the item table, I now have to insert many more rows into the "item stat relation" table to include all of the stats it needs.

Only if that item actually uses the stat. It allows an item/etc to have 0+ stats associated, and means that you would never have to update the data model for every new stat to add a column.

There's no performance benefit when you risk bad data. What you would make, will be eaten by the hacks you have to perform to get things to work.

...use MySQL functions over multiple items (i.e. to add up the "Strength" of all items owned by a Player), if some of those items don't even have a record for that stat...

Use:

   SELECT IFNULL(SUM(t.value), 0)
     FROM PLAYERS p
     JOIN ITEM_STAT_RELATION itr ON itr.player_id = t.player_id
LEFT JOIN STATS s ON s.stat_id = itr.stat_id
    WHERE p.player_id = ?
      AND ...

The LEFT JOIN is used to indicate that a supporting record may not exist, and the IFNULL is meant to handle such a situation because it's value would be null. SUM can handle NULLs - it interprets the value to be zero.

...serializing all stats into a VARCHAR or TEXT column.

Don't do this
Concatenating a list of stats will be a pain to pull out details if you want to report on a specific stat or group of stats. Due to character limits, it runs the risk of not being able to store every stat within a single column.

Not sure I understand this comment: "Currently the list is 25 stats, which means each of these tables have 50 additional columns (type/value pairs)" I'd expect either a column per stat. Or just 2 columns (key / value) and lots of extra rows

I'd have thought the player table for example would have columns as follows: Playername, Health, Strength, Intelligence etc....

Each row would then be:

MyHero, 250, 12, 11, etc...
MyOtherHero, 200, 10, 12, etc...

Adding new stats is then just a case of adding a column. For existing players you may have to data-fix the existing rows to have a default value, or some value based on their other stats.

For items that players may have you would then have an item table, that contained each item as separate rows e.g:

Itemid, itemname, health, armor, etc...
1, SwordOfDoom, 10, 200, ...
2, ShieldofReflection, 8, 300, ...

You'd then have a link table to tie each player to the items they have, so if MyHero had both items and MyOtherHero only had the Shield, we might have a Wielding table with

Player ItemID
MyHero, 1
MyHero, 2
MyOtherHero, 2

Or am I missing something?

In your case, I think the extra columns belong in the table. Otherwise, you'll end up with two tables with the same number of rows and a 1:1 mapping between them. That doesn't really simplify anything!

Conceptually, I think it's also more correct. All of those columns are pieces of data directly linked to the item in their row, so they belong together. It would be different if, say, all swords shared a common set of values. Then you'd want to say that "item Foo is a Sword, so look in the Class Attribute table to get the stats for Sword items".

If the number of stats aren't going to change, and you are sure that you won't be needing new ones, you can get away with a one table, multiple columns design, instead of the value/pair method. However, the value/pair approach is flexible, allows you to add in new stats without changing existing database schema, and save more space.

Some questions to consider

Does all characters/items have the 25 stats? If no, it is better to use value/pair
Are you going to incorporate new stats which some entity would not have? If so, use value/pair

If you do not wish to use value pair approach, you can try serialization. But this render the stats unsearchable (it's harder, to like, list all entities with Strength above 20, for example)

Mutiple tables is better, it is a normalized schema