Database design?

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

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

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 check and have all the different languages in one table. My main point is however that it should be the entities (books, e-books etc.) that references the description - not the other way around, if you need a select which returns all the different descriptions for the different entities and knows what is what use a union select the other option is to use an inherit clause - this would allow to do more or less what I believe you had originally planned. - However I due to several limitations (especially that it does not inherit constraints) in the inheritance system I would very much recommend doing 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

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

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

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
;

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

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

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

Show quoted text

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

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