Databases
StackOverflow2013
Postsdbo
POMySql table design: multiple tables vs. multiple columns
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMySql table design: multiple tables vs. multiple columns
    primarykey
    data
    text
    <p>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.</p> <p>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.</p> <p>This is starting to feel cumbersome.</p> <p>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.</p> <p>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.</p> <p>Is it worth the extra effort to keep the tables simple?</p> <hr> <p><strong>Edit:</strong> 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.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USmarc_s
    1. USNairou
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POMySql table design: multiple tables vs. multiple columns
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POMySql table design: multiple tables vs. multiple columns
      1. USveksen
      1. VTFavorite
    3. VO
      singulars
      1. POMySql table design: multiple tables vs. multiple columns
      1. This table or related slice is empty.
      1. VTUpMod
    1. COare you familiar with DB schema normalisation?
      singulars
      1. POMySql table design: multiple tables vs. multiple columns
      1. USMitch Wheat
    2. CONot really. While Googling for an answer to my question, I came across mention of it, but don't entirely know what it means.
      singulars
      1. POMySql table design: multiple tables vs. multiple columns
      1. USNairou
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload