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 having an interesting discussion with another database designer about normalization. In this example, we have a GameTitles table and each record must contain the year in which the game was released. He says 2NF mandates that everything must be normalized, so, to be compliant, the year field should be split off into a ReleaseYears table with its own primary key that is referenced by the GameTitles table. I say it should remain as a field on the GameTitles table itself.

My argument for this is that a year is just a non-primitive numeric value that is static by its very nature (ie, 2011 will always be 2011). Due to this, it serves as its own identifier and needs nothing to reference it since it is what it is. This also introduces additional maintenance since you now have to add a new year to the table just to reference it. If you prepopulate the table with a large range of years then you have extra records that potentially won't have references to them at all. This also increases database size since you now have an extra table, record overhead, and the additional primary key for the year itself. If you keep the year as a field on the GameTitles table, you eliminate all this additional maintenance and overhead.

Thoughts on this?

edit: Meant to post this on StackOverflow. Can someone vote to delete this or flag it for attention?

share|improve this question
5  
Why SO? it seems like a good fit here. –  Leigh Riffel Aug 21 '11 at 20:01
    
The question I want to ask is are you asking this about normalization or actual production needs? For production I would ask if that's a valid thing to do? –  jcolebrand Aug 21 '11 at 20:07

6 Answers 6

The other database designer is simply wrong, but your reasoning is wrong as well. Assume you start with this table, which has a single candidate key, "game_title".

Table: game_titles

game_title                      year_first_released
--
The first game                  1998
The second game                 1999
Best game: the third one        2001
The fourth game                 2003
Forty-two, the end of games     2011

You evaluate whether it's in 2NF by asking yourself these questions.

Q: First of all, is it in 1NF?

A: Yes, it is.

Q: What are the prime attributes (attributes that are part of a candidate key)?

A: "game_title" is the only prime attribute.

Q: What are the non-prime attributes?

A: "year_first_released" is the only one.

Q: Is "year_first_released" functionally dependent on the whole of "game_title", or on just a part of it?

A: The sole candidate key, "game_title", is a single column; it doesn't even have parts. So "year_first_released" is functionally dependent on the whole of "game_title".

Voilà. You've found 2NF.

You can cut through some of the formal terms by asking first whether it's in 1NF, and then answering this question.

Q: Are there any composite candidate keys?

A: No.

Voilà. You've found 2NF again.

By definition, for a table to violate 2NF, it has to have at least one candidate key that has more than one column.

Here are your reasons for rejecting your friend's opinion.

  • A year is just a non-primitive numeric value.
  • A year is static by its very nature.
  • A year serves as its own identifier.
  • A table of years introduces additional maintenance.
  • A table of years might have extra rows that aren't referenced.
  • A table of years increases database size.

None of these reasons have anything at all to do with whether a table is in 2NF.

In designing a database, it's not wrong to consider maintenance issues, database size, unreferenced rows, range constraints, and so on. It's just wrong to call those things normalization.

Oh, and that two-column table that I provided above--it's in 5NF.

share|improve this answer
2  
Nicely done. I was tempted to post an answer that said nothing other than your first sentence... "The other database designer is simply wrong", you've covered the why very well. –  Mark Storey-Smith Aug 21 '11 at 22:47

From my point of view a separate year table would only make sense if the "release year" is not a calendar year, but e.g. a fiscal year which might span multiple calendar years (e.g. going from october to october).

That table would then hold the definition (real start and end date) of the fiscal year

share|improve this answer
1  
+1 you only need a table if it is going to have attributes :) –  Jack Douglas Aug 27 '11 at 10:05

Creating a separate table for any attribute has nothing to do with normalization. 2NF, 3NF, BCNF, 4NF, 5NF are all concerned with eliminating non-key dependencies. If you remove any single attribute to a new table and replace it with a foreign key attribute then the dependencies in the table are logically going to be just the same as before - so the revised version of the table is no more or less normalized than it was before.

share|improve this answer
    
I want to add something to this, but not sure what. You're saying that moving something to a table that has a 1:1 correlation (either 1 key to exactly 1 value as in this case, or one row to one row) gives no benefit if the lookup isn't needed, right? But there is a potential lookup benefit if you rarely need the year and you're only looking at a range of 255 years or less. You could conceivably get away with a few saved bytes here, but since normally those are allocated at 4bytes anyways, this isn't a reasonable assumption. –  jcolebrand Aug 21 '11 at 20:10
    
@jcolebrand: Agree with what you say. Still the answer to the question is the same: whether you do it or not has nothing to do with normalization per se. –  sqlvogel Aug 21 '11 at 20:14
    
I concur. Like I said, mine was sortof a half-hearted "I feel like the OP is missing something here" ... because I'm not sure where to go with that concept. –  jcolebrand Aug 21 '11 at 21:12

From http://en.wikipedia.org/wiki/Second_normal_form:

a 1NF table is in 2NF if and only if, given any candidate key K and any attribute A that is not a constituent of a candidate key, A depends upon the whole of K rather than just a part of it.

You didn't indicate whether the year is part of the candidate key or not, but I'm not sure it matters, because in either case 2NF would be satisfied as far as the year is concerned.

On a practical level it is a bad idea to separate the year for all the reasons you listed.

share|improve this answer

I dislike the argument against the separate table because of its size or that it will have unused rows. Even if you put 1000 years into this table, the size will be negligible.

That said, I don't think the table is needed at all. What's the point of having a separate table for the year? This data is already in the main table and you save absolutely nothing by creating a second table.

The argument can be different for a calendar table, where each row represents a day and can have other attributes (day of week, UTC offset, whether it's a holiday, etc).

But year alone? Nah, I don't see any benefit at all... And as others have pointed out, ask them why they think that's more normalized? Or what they gain? If you're trying to write queries like

WHERE othertable.year = 2011

Instead of

WHERE dt >= 20110101 AND dt < 20120101

Then I would try to persuade you that the latter is much better for performance (assuming dt is indexed) and storage. If the coding simplicity is paramount then I would say a persisted computed column would be better than another table.

share|improve this answer

I totally agree with Catcall's answer except on one point: "year" may not be always a primitive value, but I guess that's more of a business logic concept than a database design one.

Keeping the same design, let's assume that the years should only be those years that are allowed for release. In such a way, you're not dealing with primitive numeric values, but rather a subset of them, and as such subset does not have a primitive implementation, you've got to do your own (a separate table?) and reference it (with a FK). In such a way, we're still speaking of years, but we need to manage them in a different way, because they conceptually changed their meaning. However, they're still "year of release", but conceptually different in terms of what do they mean for someone in the domain knowledge.

For this specific case, I again say that Catcall's answer is correct, but just wanted to point that out. (Sorry, don't have enough rep to comment yet.)

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.