Postgres Pro
Downloads
Home   >   mailing lists

Thread: Database design?

Database design?

From
"Johnny Jørgensen"
Date:
This may be slightly off topic, as it probably isnt all that rdbm specific. If so, holler at me, and I'll learn..
 
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 squeezed into the same table.
 
Common for all of them, however, is the need for a description. Thing is, there 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 foreign key in the description table, thus forging a relation.
 
The question (at last) is, how to have a foreign key reference more than one 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�rgensen
 
 

Re: Database design?

From
Andrew Gould
Date:
If your books, ebooks, etc are in one table, why can't
the descriptions be in one table?  The primary key
would be the unique item id from your original table.
You could have a columns for item id (the primary
key), item type (book, ebook, etc) and a separate
description column for each language.  Would this work
for you?

Best of luck,

Andrew

--- "Johnny_J�rgensen" <johnny@halfahead.dk> wrote:
> This may be slightly off topic, as it probably isnt
> all that rdbm specific. If so, holler at me, and
> I'll learn..
>
> 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 squeezed into the same table.
>
> Common for all of them, however, is the need for a
> description. Thing is, there 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 foreign
> key in the description table, thus forging a
> relation.
>
> The question (at last) is, how to have a foreign key
> reference more than one 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�rgensen
>
>


__________________________________________________
Do You Yahoo!?
Make a great connection at Yahoo! Personals.
http://personals.yahoo.com

Re: Database design?

From
Rich Shepard
Date:
On Tue, 23 Oct 2001, [ISO-8859-1] "Johnny J�rgensen" wrote:

> 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
> foreign key in the description table, thus forging a relation.
>
> The question (at last) is, how to have a foreign key reference more than
> one 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.

  If I understand your situation correctly, you may want to have an
intermediate table that provides a M-M (many-to-many) link.

  It's still early enough here that I won't try to describe the solution for
your tables, but I'll give you an example. In the accounting software I've
developed, there's a need to track project billing rates by employee (or
employee class). These rates may vary from one project to another project.
What I've done is have a table with a compound primary key: employee_id*,
project_id*, rate.

  You might have unique_id*, item_type*, language*, description. Here, your
table has a compound key of three fields, each of which is the primary key
to another relation. The 'description' field is then uniquely applied to one
-- and only one -- combination of the three key fields.

HTH,

Rich

Dr. Richard B. Shepard, President

                       Applied Ecosystem Services, Inc. (TM)
            2404 SW 22nd Street | Troutdale, OR 97060-1247 | U.S.A.
 + 1 503-667-4517 (voice) | + 1 503-667-8863 (fax) | rshepard@appl-ecosys.com
                         http://www.appl-ecosys.com

Re: Database design?

From
"Aasmund Midttun Godal"
Date:
The relation way to this imho is:

Basically what you are already doing, however I would have a field language which references a language table or a
checkand have all the different languages in one table. My main point is however that it should be the entities (books,
e-booksetc.) that references the description - not the other way around, if you need a select which returns all the
differentdescriptions for the different entities and knows what is what use a union select the other option is to use
aninherit clause - this would allow to do more or less what I believe you had originally planned. - However I due to
severallimitations (especially that it does not inherit constraints) in the inheritance system I would very much
recommenddoing it the other way. Also consider making a view of the complicated select and you are well on your way. 

regards,

Aasmund.

On Tue, 23 Oct 2001 05:10:33 -0700 (PDT), Andrew Gould <andrewgould@yahoo.com> wrote:
> If your books, ebooks, etc are in one table, why can't
> the descriptions be in one table?  The primary key
> would be the unique item id from your original table.
> You could have a columns for item id (the primary
> key), item type (book, ebook, etc) and a separate
> description column for each language.  Would this work
> for you?
>
> Best of luck,
>
> Andrew
>
> --- "Johnny_Jørgensen" <johnny@halfahead.dk> wrote:
>
>
> __________________________________________________
> Do You Yahoo!?
> Make a great connection at Yahoo! Personals.
> http://personals.yahoo.com
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Aasmund Midttun Godal

aasmund@godal.com - http://www.godal.com/
+47 40 45 20 46

Re: Database design?

From
"Ernesto Baschny"
Date:
On 23 Oct 2001 at 9:11, Johnny Jørgensen wrote:

> This may be slightly off topic, as it probably isnt all that
rdbm
> specific. If so, holler at me, and I'll learn..
>
> 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 squeezed into the same table.
>
> Common for all of them, however, is the need for a
description. Thing
> is, there 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 foreign key in the description table, thus forging a
relation.
>
> The question (at last) is, how to have a foreign key
reference more
> than one 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?

Maybe you could use inherited tables to achieve that.

CREATE TABLE items (
  id SERIAL,
  description TEXT,

  PRIMARY KEY (id)
)

CREATE TABLE books (
  isbn VARCHAR(15),
  other_field INTEGER,
  ...

) INHERITS (items);

CREATE TABLE cds (
  title VARCHAR(15),
  ...

) INHERITS (items);

With this you can define the "general" attributes in the
"items" table and more specific things in the specific tables.

The primary key for all those tables will be kept unique
across tables, since they will all refer to the same
SEQUENCE.

Maybe there are more elegant and correct solutions, I am
just beginning my jorney to the PgSQL world (this, for
example, couldn't be done with MySQL).

--
Ernesto Baschny <ernst@baschny.de>
 http://www.baschny.de - PGP Key:
http://www.baschny.de/pgp.txt
 Sao Paulo/Brasil - Stuttgart/Germany
 Ernst@IRCnet - ICQ# 2955403

Re: Database design?

From
David Link
Date:
One nice way to implement a multi-language datamodel is with Database
Views.

First you create a language table 'my_table_lang' for each significant
table, called 'my_table_base'.  And you create a view called 'my_table'.
The view joins those tables with the correct language filter.  The
Application generally only looks at the views and not the _lang or _base
tables.

This language table, x_lang will have a column for every language
specific column, needed in the x_base table, like names and
descriptions.  It will also have a language id column.  And of course a
foreign key reference to the x_base table.

create view x
select
  b.type, b.size, b.publisher,
  l.name, l,category
from
  x_base b,
  x_lang l
where
  l.key = b.langkey and
  l.langid = 'FR'   -- "French"  This would be set dynamically
                    -- from the environment


David
;

Re: Database design?

From
"Aasmund Midttun Godal"
Date:
Ok let me try to explain how I would do it:

CREATE TABLE languages (
    "language" TEXT PRIMARY KEY,
);

CREATE SEQUENCE description_seq;

CREATE TABLE descriptions (
    id PRIMARY KEY, -- You could make this default
-- curval('description_seq')
-- if you are absolutely 100% sure only one person inserts at the time.
    description TEXT NOT NULL,
    language REFERENCES languages NOT NULL,
    UNIQUE(id, language)
);

CREATE TABLE authors (
    id DEFAULT nextval('decription_seq') PRIMARY KEY,
    firstname TEXT,
    lastname TEXT NOT NULL,
    unique(firstname, lastname)
);

CREATE TABLE books (
    id DEFAULT nextval('decription_seq') PRIMARY KEY,
    title,
    author REFERENCES authors NOT NULL,
    book bytea,
    unique(title, author)
);

This is the basic structure.

Now if you like you can have a map table - although I am not sure I would.

CREATE TABLE languages (
    "language" TEXT PRIMARY KEY,
);

CREATE SEQUENCE description_seq;

CREATE TABLE map (
    id DEFAULT nextval('decription_seq') PRIMARY KEY
);

CREATE TABLE descriptions (
    id REFERENCES entities PRIMARY KEY, -- You could make this default
-- curval('description_seq')
-- if you are absolutely 100% sure only one person inserts at the time.
    description TEXT NOT NULL,
    language REFERENCES languages NOT NULL,
    UNIQUE(id, language)
);

CREATE TABLE authors (
    id REFERENCES entities PRIMARY KEY,
    firstname TEXT,
    lastname TEXT NOT NULL,
    unique(firstname, lastname)
);

CREATE TABLE books (
    id REFERENCES entities PRIMARY KEY,
    title,
    author REFERENCES authors NOT NULL,
    book bytea,
    unique(title, author)
);

On Tue, 23 Oct 2001 06:47:30 -0700 (PDT), Rich Shepard <rshepard@appl-ecosys.com> wrote:
> On Tue, 23 Oct 2001, [ISO-8859-1] "Johnny Jørgensen" wrote:
>
>
>   If I understand your situation correctly, you may want to have an
> intermediate table that provides a M-M (many-to-many) link.
>
>   It's still early enough here that I won't try to describe the solution for
> your tables, but I'll give you an example. In the accounting software I've
> developed, there's a need to track project billing rates by employee (or
> employee class). These rates may vary from one project to another project.
> What I've done is have a table with a compound primary key: employee_id*,
> project_id*, rate.
>
>   You might have unique_id*, item_type*, language*, description. Here, your
> table has a compound key of three fields, each of which is the primary key
> to another relation. The 'description' field is then uniquely applied to one
> -- and only one -- combination of the three key fields.
>
> HTH,
>
> Rich
>
> Dr. Richard B. Shepard, President
>
>                        Applied Ecosystem Services, Inc. (TM)
>             2404 SW 22nd Street | Troutdale, OR 97060-1247 | U.S.A.
>  + 1 503-667-4517 (voice) | + 1 503-667-8863 (fax) | rshepard@appl-ecosys.com
>                          http://www.appl-ecosys.com
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly

Aasmund Midttun Godal

aasmund@godal.com - http://www.godal.com/
+47 40 45 20 46

Re: Database design?

From
"Markus Meyer"
Date:
I suggest using inherited tables, but only with the id in the "items" table.
Many descriptions can then be bound to the "items" table with a foreign key.


Markus

> -----Ursprüngliche Nachricht-----
> Von: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org]Im Auftrag von Ernesto
> Baschny
> Gesendet: Dienstag, 23. Oktober 2001 16:43
> An: Johnny Jørgensen
> Cc: pgsql-general@postgresql.org
> Betreff: Re: [GENERAL] Database design?
>
>
> On 23 Oct 2001 at 9:11, Johnny Jørgensen wrote:
>
> > This may be slightly off topic, as it probably isnt all that
> rdbm
> > specific. If so, holler at me, and I'll learn..
> >
> > 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 squeezed into the same table.
> >
> > Common for all of them, however, is the need for a
> description. Thing
> > is, there 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 foreign key in the description table, thus forging a
> relation.
> >
> > The question (at last) is, how to have a foreign key
> reference more
> > than one 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?
>
> Maybe you could use inherited tables to achieve that.
>
> CREATE TABLE items (
>   id SERIAL,
>   description TEXT,
>
>   PRIMARY KEY (id)
> )
>
> CREATE TABLE books (
>   isbn VARCHAR(15),
>   other_field INTEGER,
>   ...
>
> ) INHERITS (items);
>
> CREATE TABLE cds (
>   title VARCHAR(15),
>   ...
>
> ) INHERITS (items);
>
> With this you can define the "general" attributes in the
> "items" table and more specific things in the specific tables.
>
> The primary key for all those tables will be kept unique
> across tables, since they will all refer to the same
> SEQUENCE.
>
> Maybe there are more elegant and correct solutions, I am
> just beginning my jorney to the PgSQL world (this, for
> example, couldn't be done with MySQL).
>
> --
> Ernesto Baschny <ernst@baschny.de>
>  http://www.baschny.de - PGP Key:
> http://www.baschny.de/pgp.txt
>  Sao Paulo/Brasil - Stuttgart/Germany
>  Ernst@IRCnet - ICQ# 2955403
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>

Re: Database design?

From
"Aasmund Midttun Godal"
Date:
You can insert values into this structure which have the same id...

INSERT INTO books (id) values (1);
INSERT INTO cds (id) values (1);

Which may be a problem (which may fix :)

Regards,

Aasmund.
On Tue, 23 Oct 2001 16:43:29 +0200, "Ernesto Baschny" <ernst@baschny.de> wrote:
> On 23 Oct 2001 at 9:11, Johnny Jørgensen wrote:
>
> rdbm
> book,author,country,cd,publisher)
> therefore can't
> description. Thing
> different
> i've figured
> tables as
> relation.
> reference more
> e-book, a
> them.
> description
>
> Maybe you could use inherited tables to achieve that.
>
> CREATE TABLE items (
>   id SERIAL,
>   description TEXT,
>
>   PRIMARY KEY (id)
> )
>
> CREATE TABLE books (
>   isbn VARCHAR(15),
>   other_field INTEGER,
>   ...
>
> ) INHERITS (items);
>
> CREATE TABLE cds (
>   title VARCHAR(15),
>   ...
>
> ) INHERITS (items);
>
> With this you can define the "general" attributes in the
> "items" table and more specific things in the specific tables.
>
> The primary key for all those tables will be kept unique
> across tables, since they will all refer to the same
> SEQUENCE.
>
> Maybe there are more elegant and correct solutions, I am
> just beginning my jorney to the PgSQL world (this, for
> example, couldn't be done with MySQL).
>
> --
> Ernesto Baschny <ernst@baschny.de>
>  http://www.baschny.de - PGP Key:
> http://www.baschny.de/pgp.txt
>  Sao Paulo/Brasil - Stuttgart/Germany
>  Ernst@IRCnet - ICQ# 2955403
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org

Aasmund Midttun Godal

aasmund@godal.com - http://www.godal.com/
+47 40 45 20 46

Re: Database design?

From
"Thurstan R. McDougle"
Date:
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).

Re: Database design?

From
"Aasmund Midttun Godal"
Date:
Err. CREATE TABLE entities that is.
On Tue, 23 Oct 2001 16:26:43 GMT, "Aasmund Midttun Godal" <postgresql@envisity.com> wrote:
> Ok let me try to explain how I would do it:
>
> CREATE TABLE languages (
>     "language" TEXT PRIMARY KEY,
> );
>
> CREATE SEQUENCE description_seq;
>
> CREATE TABLE descriptions (
>     id PRIMARY KEY, -- You could make this default
> -- curval('description_seq')
> -- if you are absolutely 100% sure only one person inserts at the time.
>     description TEXT NOT NULL,
>     language REFERENCES languages NOT NULL,
>     UNIQUE(id, language)
> );
>
> CREATE TABLE authors (
>     id DEFAULT nextval('decription_seq') PRIMARY KEY,
>     firstname TEXT,
>     lastname TEXT NOT NULL,
>     unique(firstname, lastname)
> );
>
> CREATE TABLE books (
>     id DEFAULT nextval('decription_seq') PRIMARY KEY,
>     title,
>     author REFERENCES authors NOT NULL,
>     book bytea,
>     unique(title, author)
> );
>
> This is the basic structure.
>
> Now if you like you can have a map table - although I am not sure I would.
>
> CREATE TABLE languages (
>     "language" TEXT PRIMARY KEY,
> );
>
> CREATE SEQUENCE description_seq;
>
> CREATE TABLE entities (
>     id DEFAULT nextval('decription_seq') PRIMARY KEY
> );
>
> CREATE TABLE descriptions (
>     id REFERENCES entities PRIMARY KEY, -- You could make this default
> -- curval('description_seq')
> -- if you are absolutely 100% sure only one person inserts at the time.
>     description TEXT NOT NULL,
>     language REFERENCES languages NOT NULL,
>     UNIQUE(id, language)
> );
>
> CREATE TABLE authors (
>     id REFERENCES entities PRIMARY KEY,
>     firstname TEXT,
>     lastname TEXT NOT NULL,
>     unique(firstname, lastname)
> );
>
> CREATE TABLE books (
>     id REFERENCES entities PRIMARY KEY,
>     title,
>     author REFERENCES authors NOT NULL,
>     book bytea,
>     unique(title, author)
> );
>
> On Tue, 23 Oct 2001 06:47:30 -0700 (PDT), Rich Shepard <rshepard@appl-ecosys.com> wrote:
>
> Aasmund Midttun Godal
>
> aasmund@godal.com - http://www.godal.com/
> +47 40 45 20 46
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html

Aasmund Midttun Godal

aasmund@godal.com - http://www.godal.com/
+47 40 45 20 46