Need help in designing some tables

[DBGuy007]

Need your help:)

Got the following problem:
A French vocable got one or more English vocable equivalents and vice versa.
What is the best way to store these information in table(s)?

Thanks for your help in advance.
Best regards,

DBGuy007

[Salem]

What options have you come up with so far?

You know, like a short list of possible candidates.

[DBGuy007]

I thougt of 2 possibilites:
Possibility 1.
Table 1: French vocabulary
Table 2: English vocabulary
Table 3: Translation table containing primary keys of table 1 and 2

Possibility 2.
French vocabulary and English vocabulary in one table where the French and English fields contain one or more vocables delimited by a ';'

Is there another possibility I haven't thought of?

Thanks in advance.
Regards,

DBGuy007

[Salem]

Option 2 sounds good.

[Fest3er]

Considering that translation/interpretation is usually one-to-many, you might be better off with option 1, where you have your vocabularies cleanly stored in the two language tables and your third table defines the relation (translation/interpretation) between the languages. This would also make it easier (in a manner of speaking) to add more languages.

[tesuji]

Hi, I am back again.

Because of "A French vocable got one or more English vocable equivalents and vice versa." there exists a many-to-many relationship between both entities. Therefore this is the correct solution:

Possibility 1.
Table 1: French vocabulary
Table 2: English vocabulary
Table 3: Translation table containing primary keys of table 1 and 2
DBGuy007

Possibility 2 is highly erroneous because it violates first normal form, creates anomalies, does not allow to creating effective sql- queries. It topsy-turvifys Codd's relational theory completely.

-- tesu