-1

Possible Duplicate:
Should each and every table have a primary key?

I've 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?

Community
  • 1
  • 1
Its me
  • 11
  • 1
  • 1
  • Hm, you forgot to define a question! – home Sep 15 '11 at 14:13
  • 1
    As hard as you tried to copy the placeholder text, you forgot the period at the end. – BoltClock Sep 15 '11 at 14:14
  • I can't give this is as an answer as it's been closed, and of course this is a bit late, but...what it looks like you are describing is a bridge table. A bridge table is quite frequently nothing BUT a composite primary key. If you make the `itemSubscribed` and `emailAddress` columns together be your primary key, you don't have any duplicates and it is in 3NF. An autogenerated primary key is the very LAST thing you want to do with a bridge table, as it only causes confusion and extra work, while actually encouraging duplication. – jmoreno Jun 21 '12 at 01:14

3 Answers3

1

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.

Walter Mitty
  • 18,205
  • 2
  • 28
  • 58
0

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.

Allan
  • 17,141
  • 4
  • 52
  • 69
0

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.

HLGEM
  • 94,695
  • 15
  • 113
  • 186