DB Normalization question
Posted: Sun Feb 08, 2004 6:23 pm
I'll be the first to admit, I have often relied on others to handle db setup for my projects and merely conformed my code to work effectively with their physical model. Now I'm working on my own project, and I want to make sure that I have properly normalized my db. My target is 4NF.
Here's the tables in question. There are actually 2 sets like this, but one example will suffice (I have eliminated some non-relevant attributes):
So, here's the question... How do I set up the join table correctly for 4NF, given that it is a Many-to-Many relationship (articles can be written by multiple authors, authors can write multiple articles). I'm thinking that this would be the proper join table for 4NF, but I'm not sure.
If it was a Many-to-One relationship, I would eliminate the AuthorArticleID attribute, but I'm thinking that since it's Many-to-Many, the attribute is needed. An example of the data would be:
I have read through the O'Reilly Java DB Best Practices ch2 pdf and several other sources at hand. However, I admit I'm still uncertain. I think the PK has to be there since neither of the other two field is necessarily unique, but I want to be certain.
For those who have the O'Reilly chapter:
I see that in the O'Reilly example - the ReviewerGenre join table to be specific - they don't use a new PK. Instead, they call both the reviewerID and the code as PKs. It is my understanding that this mmust be an error. They make acceptable FKs, but PKs have to be unique, so neither would be acceptable to use as PKs.
Thanks in advance for any help.
peace
Go Leafs Go!!!!
Here's the tables in question. There are actually 2 sets like this, but one example will suffice (I have eliminated some non-relevant attributes):
Code: Select all
Article
ArticleID (PK) BIGINT
Title VARCHAR (100)
Text CLOB
Author
AuthorCode (PK) CHAR (3)
Name VARCHAR (50)
Email VARCHAR (150)Code: Select all
ArticleAuthor
AuthorArticleID (PK) BIGINT
ArticleID (FK) BIGINT
AuthorCode (FK) CHAR (3)Code: Select all
ArticleAuthor
101 1 ABC
102 1 DEF
103 2 SSS
104 3 DEF
105 4 XYZFor those who have the O'Reilly chapter:
I see that in the O'Reilly example - the ReviewerGenre join table to be specific - they don't use a new PK. Instead, they call both the reviewerID and the code as PKs. It is my understanding that this mmust be an error. They make acceptable FKs, but PKs have to be unique, so neither would be acceptable to use as PKs.
Thanks in advance for any help.
peace
Go Leafs Go!!!!