CODEWITHSUNDEEP
databasedatabase-normalization
isharailanga
isharailanga

Reputation: 595

Normalization UNF to 1NF

When normalizing a relation with multi-value attributes, we decompose it as a new relation with the primary key field and the values from the multi-valued attribute.

The problem I have is, what is the primary key for that newly created attribute?

If we use the same old primary key as the new one, since we repeat the values there with the values from the multi-valued attribute, how can we have unique values for the primary key field here?

I have a table with customer_names, their hobbies and so on. Some people have more than 1 hobby, so now it's a multi-valued attribute. Now even if I create a new relation only with customer_name & one hobby at a time, how can I add details of the people say who have 3 hobbies?

Upvotes: 2

Views: 1607

Answers (2)

reaanb
reaanb

Reputation: 10065

From a pure normalization point of view, the new relation's key would be a combination of the original relation's key and the new single-valued attribute.

For example, if you have a relation (Person PK, Name, Hobbies), where Hobbies is a list, you can normalize that into (Person PK, Name) and (Person PK, Hobby PK).

The introduction of a surrogate key is a separate issue and while it's often done in practice while normalizing, it's not part of the concept. Hobby names aren't ideal to use as a key: mostly, they're not unique - there are synonyms, language and syntax variations. They're also likely to change and not compact. Thus, a surrogate key may be a good idea.

Upvotes: 1

Tab Alleman
Tab Alleman

Reputation: 31785

When you create the lookup table for the new attribute, you don't repeat the value in the lookup table. And usually you add an artificial PK in the form of an IDENTITY column.

For example: You have a table with a bunch of addresses. There are many addresses with a common city.

You decide to normalize by creating a city table. You only add a given city to the City table ONCE. Not once for each address.

EDIT: in the scenario described in your comment, you could create a Hobbies table with CustomerID and HobbyName.

CustomerID would be a foreign key that references the Primary Key in the Customers table.

EDIT 2: As Beth and I mentioned in comments, it seems you want a many-to-many relationship.

Create a Hobbies table, which has only one row per unique Hobby. If two or more customers have the same Hobby, you still only list the hobby in the table once. Use an Identity column to create a HobbyID as the Primary Key.

Then create a CustomerHobby table. It has CustomerID and HobbyID. Both are foreign keys that reference the primary keys of the Customers table and Hobbies table, respectively.

That's it.

Upvotes: 2

Related Questions