primary keys, junction tables, basic data base design questions
Hi.
Should a junction tables have a primary key? If so, should an additional auto number field be added as a pk or should the two foreign keys be used together as a pk?
I'm asking because I was told by an instructor that a junction table does not need a pk. But I was just thinking (usually a dangerous thing!) that one doesn't want to repeat the identical connection between two tables. Using a primary key would prevent this.
Also, with regualar data table (ie not junction tables), I always define an autonumber field to act as a pk so that it can be equated with ease to a fk from another table. But doesn't this open my tables up to identical listings differentiated artificially by an auto number index?
Please clarify.
Thanks!
Andy
Andy Kohlenberg
Jerusalem, Israel
Peter J. Boettcher posted this at 14:44 — 6th December 2001.
They have: 812 posts
Joined: Feb 2000
Andy,
Remember, there is really no one RIGHT way of doing things, there's definately many wrong ways.
There is no rule saying that you can't have an autonumber PK in a junction table. I do this all the time, since it could end up saving a lot of headaches later on.
As for having the autonumber field in all your other tables, I also do this all the time for the same reason. It shouldn't be a problem if your queries are all setup properly.
PJ | Are we there yet?
pjboettcher.com
m1l posted this at 15:05 — 6th December 2001.
They have: 29 posts
Joined: Sep 2001
Andy,
Peter is correct in what he says, there is always more than one way to skin a cat, BUT, you are saying an instructor told you and this is because he is telling you true database normalisation and the reason for database normalisation is to reduce redundancy and having a field on the junction table that is just there to be a primary key is redundancy because it isn't needed.
As for non junction table, it is always good practice to have a primary key on them.
If you're interested in normalisation, I did a quick search and found this. I only skimmed the text but it seems reasonable.
http://home.earthlink.net/~billkent/Doc/simple5.htm
Mike
Mark Hensler posted this at 17:01 — 6th December 2001.
He has: 4,048 posts
Joined: Aug 2000
I normalize as best I can, but I never sacrafice my primary key. It just makes me feel all warm and fuzzy inside.
akohl posted this at 10:27 — 7th December 2001.
They have: 117 posts
Joined: Feb 2001
Thanks, especially for that article. I started reading it and it looks like its going to answer some of my questions.
I'll post again after I read it.
Andy Kohlenberg
Jerusalem, Israel
Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.