Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
2  
clolumns. Is that a NoSQL thing? –  Phil Oct 10 '12 at 0:12
1  
Nobody wants to say anything about that GUID column that's an INT? :-) –  Marian Oct 10 '12 at 8:19
2  
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 '12 at 8:41
    
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 '12 at 23:07

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

share|improve this answer
1  
Yep, and would you worry not about searching in it. –  Marian Oct 10 '12 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 '12 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 '12 at 21:21
    
True. Like most questions, more information is needed. –  mrdenny Oct 10 '12 at 23:06

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

share|improve this answer

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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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