Re: relational db design question

From: Bob Badour <>
Date: Thu, 08 Feb 2007 20:27:29 GMT
Message-ID: <RILyh.4065$> 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