Table Design and primary key suggestions

[DanC]

If this is the wrong forum, please move this thread.

I've got two tables:

email_table
columns are emailNo, JobNo, emailBody, etc.
and
email_attachments
columns (right now) are EmailNo and AttachmentPath
There is a one to many relationship from email_table to email_attachments.

Primary key on email_table is emailNo. I need advice on how to designate the primary key on email_attachments. Is it advisable to use the combination of EmailNo and AttachmentPath for the primary key, or should I create a third column that is an identity field? The data in the AttachmentPath field is just a file location, ie //server3/client/job12/attachment.pdf.

Any suggestions are welcome.

-Dan

 

[r937]

it is not necessary, despite what some people will tell you, for every table to have a primary key

the only reason to have a primary key is if there will be a foreign key referencing it

in other words, in your example, the email_attachments table will require a primary key only if it has a child table -- which you didn't mention (and which i can't imagine)

the email_attachments table may require an index, for performance reasons, but that is actually a separate issue

it may also require a unique constraint, so that you cannot insert a duplicate row with the same EmailNo and AttachmentPath, but that too is a separate issue

now, lest you think i'm totally bonkers, yes, i do understand that a primary key automatically creates a unique index, and thus, with one swell foop, you get two things which you probably require for your particular situation

the reason i am going to such elaborate lengths to make a distinction between primary keys and indexes and unique constraints, is because of the unfortunate notion, which somehow seems to have gotten around, that a table "needs" a primary key

this causes some people to get confused and then do something silly -- no offence, dan -- like creating a separate identity column just so that they can call it the primary key

that's probably the worst thing you can do, because then in order to have good performance and to ensure uniqueness, you would also have to declare an index and a unque constraint!!

having said all of the above, i would nevertheless recommend that you go ahead and declare a primary consisting of EmailNo and AttachmentPath

just realize why you're doing it




rudy

http://r937.com/

 

[DanC]

thanks for the advice. This is exactly what my opinion on the matter was, I was having a debate with a programmer who wanted the additional column. My thinking is that a primary key that doesn't link to anything, isn't much of a primary key at all.

Thanks again

-Dan

 

[r937]

actually, it should be "a primary key that doesn't have anything linked to it" -- it's the foreign keys that do the linking

don't jump all over your programmer, it is a very common mistake


rudy

 

[DanC]

no jumping going on here. We were just debating about how the structure would be set up.