Thread: table has a many to many relationship with itself ... ?

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

On Tue, Jun 13, 2006 at 05:01:01PM +0200, Daniel McBrearty wrote:
> create table translations (
>  id serial primary key
> );

> insert into table translations ... insert what?

insert into translations default values;

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

I wouldn't use an array.  I think arrays are best for data that can be
considered one chunk of stuff by postgresql, with the arrayness of
that chunk of stuff only mattering to the application.