relational db design question
Date: 8 Feb 2007 11:05:19 -0800
Message-ID: <1170961518.911571.61730_at_l53g2000cwa.googlegroups.com>
Hello,
I have a relational database design question that I cannot seem to solve in an aesthatically pleasingly way; I was hoping someone could help me with.
I am trying to create a recipe database, in which I would like to associate each recipe with a reference (the reference can be a book, newspaper, person, magazine, etc...) Because each reference is so different, and can be used multiple times, I have broken a single reference into two tables.
For instance, a Cook Book reference is:
· Table: Authors { AuthorID, AuthorName }
· Table: BookAuthors { BookID, AuthorID, (OPTIONAL) NameOrder }
· Table: Books { BookID, Title}
· Table: BookReference { RefID, BookID, PageNumber, OtherInfo }
The same would be done for Magazines, Newspapers, and People, with the
*Reference table being the most interesting (to this discussion) one
to change:
· Table: PeopleReference { RefID, PersonID,
InfoOnWhyPersonGaveYouThisRecipe }
· Table: NewspaperReference {RefID, NewspaperID, Date, Section/Page }
· Table: MagazineReference {RefID, MagID, Date, Page }
So, my question, which I am sure you are already answering, is how do
I map these RefID's to my Recipe, when each one will be a primary key,
incremented in autonomy? The direction I am heading, which makes me
shutter like fingers screaching down a chalkboard, is to make my
Recipe Table look like:
· Table: Recipes {RecipeID, Name, ..., RefID, RefTable, ... }
· Where RefID (refers to any RefID in any *Reference table) and
RefTable identifies which table
That is, should I store the RefTable type in the Recipe Table?
Alternatively, but equally as ugly, I could break the info out into another table (but honestly don't know what that saves).
If this is not the way to do this (and I am hoping it is not), what is the correct way to design this?
Thanks Received on Thu Feb 08 2007 - 20:05:19 CET