Re: relational db design question
Date: Thu, 08 Feb 2007 23:32:09 GMT
Message-ID: <ZpOyh.42181$Gr2.6429_at_newssvr21.news.prodigy.net>
<chezball_at_gmail.com> wrote in message
news: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?
If this IS the way to do this then my next question is, how does one do lookups based upon this info? What would the SQL statement that would return all the relevant information about, say a cookbook, be?
Thanks
Your question is more a matter of implementation rather than theory and it would be better to post to a product-specific group. However, I will try to answer your question. You are correct in making Magazine, Book, etc subtypes of Reference, at least at the logical level. You can implement this in three ways:
- one big table allowing nulls
- many subtype tables
- a supertype/subtype tables with discriminator
Google "implementing subtypes".
As for your query, this will be implementation specific, too. Does your implementation support CASE or IF within a SELECT? Received on Fri Feb 09 2007 - 00:32:09 CET