Need help in designing some tables
 

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

Re: Need help in designing some tables
 

What options have you come up with so far?

You know, like a short list of possible candidates.

Re: Need help in designing some tables
 

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

Re: Need help in designing some tables
 

Option 2 sounds good.

Re: Need help in designing some tables
 

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.

Re: Need help in designing some tables
 

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