table has many to many relationship with itself - how to implement? - Mailing list pgsql-general

Hi all,

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