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

database-designnormalization

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.

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

Related Solutions

Multiple values in lookup fields

For a single student, why would you have multiple selection for race? This for the situation you mentioned as example.

I'd see both options valid (lookup with multiple selection or radio buttons with true-false) depending on the situation you need to display. What you show in the front is not identical with what tables you have in the database. Do your tables to fit simple normalization rules and then just build queries and views to display in the Access UI. Read about normalization and normal forms and design your tables accordingly (I prefer by default 3rd normal form, but it's not mandatory, you could increase/decrease the form level as you see fit).

I think that this tutorial is done with Access users in mind.

You can find some good free data models here: Free data models. Some of them even have attached Access databases.

PS: more of a comment than an answer, but too verbose.

Separate or Combine 2 Tables with Similar Attributes

I'd say "Customer" and "Employee" are two roles a person can play in your system. Thus, I'd add concept of "Role" to your design. Then you can store customer related attributes in one table, and employee related attributes in another. You will have something like

User (user_id, name, ...)
Role (role_id, description)
UserRole (user_role_id, user_id,role_id, date_from, date_thru) 
-- note, dates are quite important(at least date_from should be not null)
CustomerRoleAttribute(user_role_id, NewsUpdateSubscription)
EmployeeRoleAttribute(user_role_id, salary)

Also, to be absolutely correct, Customer and Employee roles require second part - for instance, I'm a customer of AAA company, and an employee of BBB, so my role is defined by relationship of certain type with other user (which is organization in case of "Employment" relationship).

You can find out more about this approach in "The Data Model Resource Book" by Len Silverston

Best Answer

Related Solutions

Multiple values in lookup fields

Separate or Combine 2 Tables with Similar Attributes

Related Question