Post by Hendry TaylorI have a table A in which I have a column 'Languages spoken' which
needs to store the references to multiple rows in table 'languages'.
Post by Hendry TaylorHow would I configure that the column in table a is able to store multiple index references?
You have a M:N relationship here. Typically this uses 3 tables:
Person (your table A), Languages (your table languages) and
Person_Languages (a new table) to tie the other two together. Don't
have a 'Languages spoken' column in the Person table, nor a 'Spoken by'
column in the Languages table.
The Person_Languages table contains at least two columns: the
primary key of the Person table and the primary key of the Languages
table. You put one row in here for each language a person speaks.
Some people (e.g. babies, those who cannot speak) won't have any
entry in this table. If you wish to distinguish between written
and spoken languages, how well someone speaks/reads/writes the
language, or record the language the person wants billing statements
in, you might use additional columns here. The primary key of the
Person_Languages table is the combination of the primary keys of
the 2 tables it ties together (person_id, lang_id).
The primary key of the Person table is typically a number (Account
number, Customer ID, Employee number, etc.). Names change, and
there are also too many different forms of a name to use it as a
primary key (Robert Lee, Robert E. Lee, Robert Erwin Lee, Bobby
Lee, etc.). Also, there are plenty of pairs of different people
with the same name, even if you include a generational suffix (Sr.,
Jr., III, IV, etc.) as part of the name, especially for names like
"John Smith".
The primary key of the Language table probably ought to also be a
number, as names of languages have different spellings in other
languages:
English, inglés, anglais, inglese
French, francés, français, francese
Spanish, español, espagnol, spagnolo
(note: if your newsreader doesn't understand UTF-8, some
accented letters in the names above may appear garbled.)
Example:
Fred K. Smith speaks Spanish.
Khan Noonien Singh speaks English and Klingon.
Jean-Luc Picard speaks English and French.
Jane Smith is 3 months old and doesn't talk yet.
Person
person_id name
_________ ____
101 Fred K. Smith
102 Barak Obama
103 Khan Noonien Singh
104 Jean-Luc Picard
106 Jane Smith
Languages
lang_id language_name
_______ _____________
1 English
2 French
3 Klingon
4 Spanish
5 Bahasa Indonesia
Person_Languages
person_id lang_id
_________ _______
101 4
102 1
102 5
103 1
103 3
104 1
104 2