Re: Database design? - Mailing list pgsql-general

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