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

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 like specifying my own sequence names, instead of using "serial", plus
using a default this way lets me insert an integer directly, when
necessary, or letting it default, but you can use serial, if you want).

That lets you insert rows for multiple to/from pairs. Also, some words
have multiple meanings, or more than one word has the same meaning. For
example, the English word "room" can be either "camera" or "stanza" in
Italian.

testdb1=> select * from phrases;
id | language | content
----+----------+-----------
1 | 1 | the book
2 | 2 | le livre
3 | 3 | el libro
4 | 4 | il libro
5 | 1 | the room
6 | 4 | la stanza
7 | 4 | la camera
(7 rows)

testdb1=> insert into translations (lang1_id, lang2_id) values (1, 2);
INSERT 666949 1
testdb1=> insert into translations (lang1_id, lang2_id) values (1, 3);
INSERT 666950 1
testdb1=> insert into translations (lang1_id, lang2_id) values (1, 4);
INSERT 666953 1
testdb1=> insert into translations (lang1_id, lang2_id) values (5, 6);
INSERT 666954 1
testdb1=> insert into translations (lang1_id, lang2_id) values (5, 7);
INSERT 666955 1

Then, you can do this:
select p.content from phrases p where p.id in (select lang2_id from
translations where lang1_id = 5);
content
-----------
la stanza
la camera
(2 rows)

I assume that this is a fairly simple "phrasebook" type of data set.
Partly, the structure depends on how you intend to access the data after
you build it.

Just an idea.

Susan

"Daniel McBrearty"
<danielmcbrearty(at)gmail(dot) To: pgsql-general(at)postgresql(dot)org
com> cc:
Sent by: Subject: [GENERAL] table has many to many relationship with itself - how to
implement?

pgsql-general-owner(at)pos |-------------------|
tgresql.org | [ ] Expand Groups |
|-------------------|

06/14/2006 01:53
AM

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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

----------------------------------------------------------------------------------------------
Simply protected storage solutions ensure that your information is
automatically safe, readily available and always there, visit us at http://www.overlandstorage.com
----------------------------------------------------------------------------------------------