Normalization: Is it considered compliant to split static, numeric values like a year into their own table?

Question

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?

Answer 1

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

Answer 2

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

Answer 3

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.

Answer 4

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.

Answer 5

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.

Answer 6

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.