Name for a “lookup” table with values in separate rows

Posted on

Question :

I am looking to put a name on a particular situation. When you remove a comma-delimited column and split it into separate rows in a new table, what is that type of table called?

In the example below, I could keep FavoriteFoods in the top table (Albert Einstein would have “Apple Pie, Soup”) but I want each food to have its own row.

Is the second table a “cross join” table that hasn’t been normalized?

Please share if you know the name for this. I will do more research once I know the name.

PersonID and Name in table above and PersonID and FavoriteFood in table below

Answer :

Removing repeating groups (your comma delimited values) and moving this data into another table where each value is a row in that table is called normalization. In particular, you’re moving from 0 Normal Form (0NF) to a higher normal form, (1NF or higher, depending on what functional dependencies remain).

In your specific example, once you split out the favourite foods into a child table you might be in either 1NF or 3NF, depending on whether you also have a “master table” of food types.

The new child table where the favourite foods are recorded might be considered an intersection table, particularly if you also have a master table of all of the foods listed one record per type of food. This is the typical way in which a many-to-many relationship is implemented in a relational database.

If you have this master table of food types, you would be in 3NF. If you don’t your child table might not be considered to be 3NF, just 1NF, because the list of foods is subject to update anomalies and possibly delete anomalies – depending on how your system views foods themselves, independent of their relationship to people.

Leave a Reply

Your email address will not be published. Required fields are marked *