Does my table violate normalization rules?

Question

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

-- ----------------------------
-- Table structure for `items`
-- ----------------------------
DROP TABLE IF EXISTS `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`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

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

'110', '6797', '1', '6', 
     'b6#1#0#0#0d0+1,3ca#2412#0#0#0d0+9234,3cb#1#0#0#0d0+1,3cc#1#0#0#0d0+1,
      3cd#a#0#0#0d0+10,3ce#1#0#0#0d0+1'

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?

Answer 1

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

Answer 2

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.

Answer 3

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.

Answer 4

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.