Re: relational db design question

On Feb 8, 1:05 pm, chezb..._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.
As you have created separate tables for Books, Magazines and Newspapers, a rhetorical question is why have you not created tables for Male Authors and Female Authors ? The schemata needs to be generalized because Book , Magazine and Newspaper is an attribute ( publication method) not a relations.

You need also to be aware that Publications have components ( Articles ?) and you need to determine if you need to record some information at the Article level of detail.

Try a schemata somthing like:

Table: Persons { PersonId , PersonName } Table: Publications { PublicationId , PublicationName} primary key ( PublicationId )
Table: Publication_isa_Book { PublicationId , ISBN } primary key ( PublicationId ) , alternate key (ISBN), foreign key ( PublicationId ) references Publications Table: Publication_isa_Periodical { PublicationId , PublishDate } primary key ( PublicationId ) , foreign key ( PublicationId ) references Publications
Table: PublicationArticles (PublicationId, Section/Page , ArticleName ) primary key ( PublicationId , Section/Page ) foreign key ( PublicationId ) references Publications Table: PublicationArticleAuthors (PublicationId, Section/Page, AuthorId)
Table: References { RefID, PersonID, ReferenceDefinition } Table: ReferenceArticles (RefID, PublicationId, Section/Page )

You need to detemine if it is interesting that the same Article can appear in more than one Publication. For example, the short story "Picnic on Nearside" has been published in Magazine "Fantasy & Science Fiction" , anthology "Tales in Space", collection "Picnic on Nearside' and collection "The John Varley Reader". See John Varley's web page at http://www.varley.net/Pages/ Bibliography.htm

Some recommended reading:

The Data Modeling Handbook : A Best-Practice Approach to Building Quality Data Models by Michael Reingruber, William W. Gregory (Contributor)
Publisher: John Wiley & Sons; ISBN: 0471052906; (December 1994) http://www.amazon.com/exec/obidos/ASIN/0471052906/qid=1027712591/sr=1-1/ref=sr_1_1/102-9038493-1321736

HANDBOOK OF RELATIONAL DATABASE DESIGN
by C. C. Fleming and Barbara von Halle
Addison-Wesley; ISBN 0-201-11434-8.
http://www.amazon.com/exec/obidos/search-handle-form/104-9318903-3039129 Received on Sun Feb 11 2007 - 18:22:44 CET