I am developing an emulator for a game. My item table looks like this:

-- ----------------------------
-- Table structure for `items`
-- ----------------------------
CREATE TABLE `items` (
  `guid` int(11) NOT NULL,
  `template` int(11) NOT NULL,
  `quantity` int(11) NOT NULL,
  `position` int(11) NOT NULL,
  `stats` text NOT NULL,
  UNIQUE KEY `guid` (`guid`)

so some rows look like this (row is wrapped for readability):

'110', '6797', '1', '6', 

As you can see the long mess at the end is only one column - they are stats. I think this field could be divided into individual stats in more columns. Would that violate normalization rules? Should columns hold only indivisible attributes?

  • 2
    clolumns. Is that a NoSQL thing?
    – Philᵀᴹ
    Oct 10, 2012 at 0:12
  • 2
    Nobody wants to say anything about that GUID column that's an INT? :-)
    – Marian
    Oct 10, 2012 at 8:19
  • 3
    Ah..wait, I didn't see it well. The GUID column is declared as an INT, but inserted as CHAR. It's a true globetrotter in the planet of data types.
    – Marian
    Oct 10, 2012 at 8:41
  • 1
    So a question has come up in my answer below which will influence the design a lot. Do you need to search based on the stats column, or are you just stuffing data into it and returning data based on other columns (like the guid column)?
    – mrdenny
    Oct 10, 2012 at 23:07

4 Answers 4


don't add more stat columns unless you are absolutely sure the required # of columns wont grow, tables tend to get unmanageable that way.

if you need to work with the individual stats in your comma separated text field, you probably should break them off into their own table where each row has 1 stat, make guid+statid your primary key and then you can make the stat column something more reasonable like a varchar (20). This might take up a little more diskspace but it should be faster working with individual stats...


Your table is definitely not in normal form. As WebChemist noted, I would break the stats out into a separate table with guid and a statistics id as the primary key. This would be a weak table.

Looking at you individual stats entries it appears they contain separate fields, possibly beginning with at statistics id of some sort.. If break your statistics table you will be able to run analysis with the database. This will allow you to run already validate statistical functions against the data. Doing this may perform better than doing the same calculations in the application.

Adding, editing, and removing statistics in the normalized form should perform better, especially as the number of statistics per guid increases. Validations should also be simpler.


The relational model of data allows arbitrarily complex data types. If values have internal structure, a relational database management system must either

  • ignore the internal structure, or
  • provide functions for manipulating the pieces.

Timestamp values have internal structure. SQL database management systems provide functions for manipulating the pieces.

If the dbms ignores the internal structure of the stats column, and just returns that column as a single value without trying to pick apart the pieces, it doesn't violate 1NF.

But storing those values in a single column also doesn't offer any real type safety. Without storing each of those chunks in a separate column (or possibly multiple columns), the dbms can't guarantee that each chunk consists of 5 pieces, with each piece having certain well-defined characteristics.

Whether type safety matters for individual chunks depends on the application. It doesn't look like it matters for your application.


I wouldn't worry to much about if a table violates specific normalization rules. You're shooting for functions fast and well. No manager is going to tell you to redesign something because it doesn't violate a specific form of normalization (and if they do, run, fast).

In this case could you break that stats column into multiple rows in the table, probably. Would doing so hurt application performance, possibly. If I was designing this system would I make that change, probably not.

  • 1
    Yep, and would you worry not about searching in it.
    – Marian
    Oct 10, 2012 at 8:42
  • Based on the table design I would say that he's not worried about querying on it. Based on the schema all queries would be against the guid column I'm guessing. If there were to be queries against the stats column then yes we need to redesign. However if the column is just to hold data (I've worked in the gaming field before, this is normal data access patterns) then having just a single field is fine.
    – mrdenny
    Oct 10, 2012 at 18:19
  • OK, so the OP needs to tell us if your guess is correct. If yes, then it doesn't matter if it's normalized or not..it matters to be the fastest way to put and get the data.
    – Marian
    Oct 10, 2012 at 21:21
  • True. Like most questions, more information is needed.
    – mrdenny
    Oct 10, 2012 at 23:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.