Primary keys & database normalization [duplicate]

This question already has answers here: Closed 11 years ago.

Possible Duplicate:

Should each and every table have a primary key?

I've 开发者_JS百科been working on a school project about Database normalization. I need help in normalizing a table that has no primary key The table I'm having difficulty with is a table for subscriptions and it's structure is like this:

itemSubscribed  emailAddress
--------------  ------------
1               a@b.com
1               b@c.com
1               a@b.com
2               x@z.com
2               aaa@b.com
3               a@b.com

---

Notice that itemSubscribed and emailAddress values may repeat, so neither can be a primary key.

This structure will work fine with my code for I can send an email to all item X subscribers when there's an update in item X but my teacher requires a normalized database and 1NF must have a primary key.

If I created an autogenerated primary key for the sake of having a primary key I can't proceed with 3NF for it requires that all columns are dependent upon the primary key, w/c is not the case.

Should I create a autogenerated primary key? Am I missing something in regards to 3NF?

---

A table with repeating rows does not represent a relation. A relation is a set of tuples. A set never has the same element in it more than once. A bag is like a set, but can have multiple instances of elements that look identical.

In the table you give us, I presume that itemSubscribed is a count, and the the two rows that have itemSubscribed equal to one with the same emailAddress describe different events.

But that is in your mind, and not visible in the data.

You are going to get into trouble with this table. In particular, there is no way to distinguish between an erroneous duplicate entry, and two valid entries that look alike.

---

Are you allowed to have the same e-mail address subscribed to one item multiple times? If not your natural key is obvious: itemSubscribed and emailAddress. Even if you chose to have an artificial primary key in this case, you'd probably want a unique index across the two columns.

---

In answer to your question, yes it is really bad not to have a primary key. The database must have a way to identify a specific record. Suppose you wanted to update the record shown below in bold but not the one italics. How would you do that without a primary key.

itemSubscribed emailAddress

---

1 a@b.com

1 b@c.com

1 a@b.com

In a database class, I would fail you if you had any table without a primary key, it is that critical to database design.

Now I suspect that you would not want to actually have the data as shown unless you had other columns that were differnt. Why do you really want two records with the same items subscribed and the same email address? It is better to have a PK or unique index to prevent this sort of bad data. I suspect you really have a natural key of both fields and just currently have bad data.