Re: table has many to many relationship with itself - how

On Wed, Jun 14, 2006 at 13:51:50 -0700,
SCassidy(at)overlandstorage(dot)com wrote:
> Starting with this:
>
> create sequence languages_seq increment by 1;
> create table languages (
> id integer primary key default nextval('languages_seq'),
> language_name varchar(100)
> );
> insert into languages (id, language_name) values (1, 'English');
> insert into languages (id, language_name) values (2, 'French');
> insert into languages (id, language_name) values (3, 'Spanish');
> insert into languages (id, language_name) values (4, 'Italian');
>
> create table phrases(
> id serial primary key,
> language integer references languages(id),
> content text
> );
> insert into phrases (language, content) values (1, 'the book');
> insert into phrases (language, content) values (2, 'le livre');
> insert into phrases (language, content) values (3, 'el libro');
> insert into phrases (language, content) values (4, 'il libro');
> insert into phrases (language, content) values (1, 'the room');
> insert into phrases (language, content) values (4, 'la stanza');
> insert into phrases (language, content) values (4, 'la camera');
>
>
> For your translations table, I would go with something like this:
>
>
> create sequence translations_seq increment by 1;
> create table translations (
> translation_id integer primary key default nextval('translations_seq'),
> lang1_id integer references phrases(id),
> lang2_id integer references phrases(id)
> );

I think you are better off putting the equivalence information in the phrases
table. (This assumes that treating translations of a phrase into various
languages forms an equivalence class.) Under this model each phrase will
be in exactly one equivalence class, so that adding an equivalence class
column to the phrase table seems like a good solution.