Re: relational db design question

From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Sun, 11 Feb 2007 18:29:14 GMT
Message-ID: <_fJzh.5237$R71.78571@ursa-nb00s0.nbnet.nb.ca>

Carl Federl wrote:

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

Those are astoundingly detailed design recommendations given the near total lack of requirements information.

> 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

Do those books describe the mind-reading methods you must have used to gather the requirements? Received on Sun Feb 11 2007 - 12:29:14 CST