Re: Database design? - Mailing list pgsql-general

Based on the list of entities I would imagine that what you should do is
have a Publication table with a field indicating that an item is a Book,
an E-Book or a CD (these are not that different!), also a field for
Country.  You then could either have the Author and Publisher as fields
(if just the name(s) is enough), or AuthorID and PublisherID fields tied
to Author and Publisher tables.
The Descriptions would then be a table that ties back to the
PublicationID.  I would tend to give this table a compound primary key
of PublicationID+LanguageID (with a table for languages) or
PublicationID+LanguageNo (1=first language, 2=second etc) with a
language name field.

Note:If you use an Author table then you might need a linking table
("BookToAuthor") to allow for items with multiple authors as in those
cases you would have a many to many relationship.

This assumes that you do not need descriptions for Author, Country and
Publisher.
If however you do have such descriptions then create a "description
master" table that is used to create a unique ID for each description
(irrespective of language) then your Descriptions table would use a key
of DescriptionID+LanguageID and your described items (book, e-book,
author, country, cd, publisher) would have a DescriptionID field that is
ties to the "description master" table.
You could have field(s) in the description master field that indicate
which table(s) it can be used on.  I say tables as a description could
surely apply to a book and the e-book (and CD) version of that book?
Also, especially for e-books, the author and publisher could be the same
individual.

"Johnny Jørgensen" wrote:
>

> I have a bunch of entities (book,e-book,author,country,cd,publisher) which =
> are quite different in relational nature, and therefore can't easily be squ=
> eezed into the same table.
>
> Common for all of them, however, is the need for a description. Thing is, t=
> here needs to be descriptions in (currently) 4 different languages, and the=
>  count may rise.
>
> This relation is of a 1-N nature for each entity, and so, i've figured out,=
>  I use the unique id from the (book, e-book, author etc) tables as a foreig=
> n key in the description table, thus forging a relation.
>
> The question (at last) is, how to have a foreign key reference more than on=
> e table? Obviously a description won't belong to an e-book, a country and a=
>  publisher at the same time, but only one of them.
>
> Is my design fundamentally unsound, should there be a description table for=
>  each of the listed entities, or what am I to do?
>
> In hope of helpful guidance,
>
> Johnny J=F8rgensen

--
This is the identity that I use for NewsGroups. Email to
this will just sit there. If you wish to email me replace
the domain with knightpiesold . co . uk (no spaces).