2

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

5

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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