Home : Products : DBMan SQL : Discussion :

Products: DBMan SQL: Discussion: Re: [jai] Database advice: Edit Log

Here is the list of edits for this post
Re: [jai] Database advice
Hi,

Let me give you more info about the three table solution hannagaijin was talking about:
You have described a many-to-many relation: One user can speak one or more languages and one language can be spoken by zero or more people. This is normally implemented by three tables as follows (note that I am not good at choosing table and column names, so I hope it makes sense):

Person: id, name, email, address, other_info (primary key is id)
Language: id, name, nr_of_native_speakers, other_info (primary key is id)
LangPers: person_id, language_id, quality, learned_at_which_age (primary key is person_id, language_id combination)

You use this by adding an entry in the LangPers for each valid Person/Language combination. Note that that table can also have a column for how well a person speaks the language (like Dutch is my native language, I speak English well and I speak a little German). You can use the table Language to store more language specific info (like the number of people that speak the language worldwide).

I agree with hennagaijin that it might be not obvious to implement something like that in DBMan SQL. But I would guess that if you are not afraid of writing your own templates and plug-ins, it is worth the try...Cool

For the user input: radio buttons and combo boxes are typically for selecting a single entry from a list. Radio boxes and multi select fields are used for selecting multiple items from a list.
You could also consider a multi page process for adding languages: show a page with all user info, a list with currently added languages (behind each language there could be a link with 'remove language from list') and a link (or button) with 'add language'. When a user clicks this link, it will come to a page with with a combobox for the language to add and possible other questions about this language for the specific person (like how well (s)he speaks it and when it was learned). By clicking 'submit', the language is added to the list for that user.

Hope this helps,

Jasper

http://www.bookings.org

Last edited by:

jaspercram: Jan 2, 2003, 1:49 PM

Edit Log: