Re: relational db design question

From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Thu, 08 Feb 2007 20:27:29 GMT
Message-ID: <RILyh.4065$R71.61499_at_ursa-nb00s0.nbnet.nb.ca>


chezball_at_gmail.com wrote:
> 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?

Beg, borrow or steal a copy of Fabian Pascal's _Practical Issues in Database Management..._ book. Received on Thu Feb 08 2007 - 21:27:29 CET

Original text of this message