From: | "Daniel McBrearty" <danielmcbrearty(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | table has a many to many relationship with itself ... ? |
Date: | 2006-06-13 15:01:01 |
Message-ID: | 9cf113670606130801o7772265fn1b7971304426e42b@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi
I have a table "phrases" that looks like this :
create table phrases(
id serial ,
language integer references langauges(id),
content text
);
Simply a word or phrase in some language.
Now I want to express the concept of a "translation". A translation is
a number of phrases from different languages that are a translation of
each other. There is nothing else to say about a translation - though
it does need to be referencable by other tables, so it needs an ID.
One way to do this is with these two tables:
create table translations (
id serial primary key
);
create table translations_to_phrases (
translation_id integer references translations(id),
phrase_id integer references phrases(id),
primary key (translation_id, phrase_id)
);
Now this actually works as a data structure; the translations table is
a bit odd, having only an id, but that is all we really need.
Can I do this though? can I create a row in translations?
insert into table translations ... insert what?
The other way to do this that I see is to lose the link table
translations_to_phrases, and then make translations
create table translations (
id serial primary key,
phrases integer[]
);
but it seems that I can no longer make postgre aware that the integers
in translations(phrases) are references.
What is the best solution?
Thanks
Daniel
--
Daniel McBrearty
email : danielmcbrearty at gmail.com
www.engoi.com : the multi - language vocab trainer
BTW : 0873928131
From | Date | Subject | |
---|---|---|---|
Next Message | Carlos H. Reimer | 2006-06-13 15:10:26 | XID comparations |
Previous Message | Tom Lane | 2006-06-13 14:59:19 | Re: pg_dump: missing pg_database entry |