Re: Database design?

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(at)appl-ecosys(dot)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(at)appl-ecosys(dot)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(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly

Aasmund Midttun Godal

aasmund(at)godal(dot)com - http://www.godal.com/
+47 40 45 20 46