Search code examples
sql-serverdatabase-normalization

Database Normalization - Field depending on another non-key field

The following is part of a table definition in SQL Server:

CREATE TABLE User     
[UserId] INT NOT NULL IDENTITY(1,1),
[EatsFruit] BIT NOT NULL DEFAULT '0',
[FavoriteFruit] NVARCHAR(50) DEFAULT NULL,

As you can imagine, UserId is the primary key. I used a simpler example here to explain my question related to the "fruit" fields.

The field EatsFruit will be either 1 or 0, depending on whether the user eats fruit or not. If EatsFruit contains a 1, then the FavoriteFruit field will include the user's favorite fruit. If EatsFruit is 0, then FavoriteFruit is not relevant and it would have to contain N/A or some similar value.

I am wondering what the best way to model this is, and whether it needs to be normalized.

Since the FavoriteFruit field depends on the content of EatsFruit, should it be separated in a different table containing UserId and FavoriteFruit? This would be cleaner, because an entry for a certain user would not appear unless the user actually eats fruits (and the content of FavoriteFruit would always be relevant). However, since the primary key of the new table would also be UserId, doesn't this mean that FavoriteFruit really depends on the UserId and should not have been separated from the main table in the first place?

What would be best practices here? Thank you so much!

Solution

  • From a pure normalization perspective, you don't want to have a field that is potentially taking up space with useless information, as you would in your example when the user doesn't eat fruit. Furthermore, you really don't want Favorite Fruit to be an NVarchar as "Melon" and "Watermelon" are different things (or are they), Or what about an entry of "Aple" on accident.

    Were it me, I would have a Fruit Table and a FavoriteFruit Association table, The FavoriteFruit table would have the fruit id and the user id. If the user didn't have a favorite fruit, no space is used. Also, I would ask whether I could get rid of "EatsFruit" and simply check for an entry in the FavoriteFruits table.

    That said, the way you have is set up, though maybe playing a little loose, is not an unpardonable sin.

    Cheers.