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)?
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?
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.
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.
No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.
Previous Thread in Database Design Forum Timeline:"Common" tables
Unsolved 
Offline 
