table design question

[chezball]

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

 

[John W. Vinson]

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).

This seems to be a classic case of "Subclassing" - one of the few
instances where one-to-one relationships are appropriate!

You have a class of entities - References - which share some
information in common; each member of this class is also a member of a
Subclass - e.g. the Class of References has some members which are
Magazine references, some which are Cookbook references, etc.

What you can do is have a References table with an autonumber RefID,
joined one-to-one to tables with the fields needed for the subclasses.
These tables would have a Long integer RefID as a primary key. You may
want some code to (say) prevent a given reference from appearing in
more than one subclass table - or you might not, if you find the same
recipe in two or more sources!

Your Recipe table would have a foreign key to the (master) References
table; your reports would have the References table left-joined to
each of the subclass tables to display whatever information is there.

John W. Vinson [MVP]

 

[chezball]

This seems to be a classic case of "Subclassing" - one of the few
instances where one-to-one relationships are appropriate!

You have a class of entities - References - which share some
information in common; each member of this class is also a member of a
Subclass - e.g. the Class of References has some members which are
Magazine references, some which are Cookbook references, etc.

What you can do is have a References table with an autonumber RefID,
joined one-to-one to tables with the fields needed for the subclasses.
These tables would have a Long integer RefID as a primary key. You may
want some code to (say) prevent a given reference from appearing in
more than one subclass table - or you might not, if you find the same
recipe in two or more sources!

Your Recipe table would have a foreign key to the (master) References
table; your reports would have the References table left-joined to
each of the subclass tables to display whatever information is there.

John W. Vinson [MVP]

Thank you. This is exactly what I was looking for.