Gossamer Forum
Home : Products : DBMan SQL : Discussion :

Database advice

Quote Reply
Database advice
I’m creating a database (using DBManSQL of course) of people (worldwide) and the languages they can speak.

I have been doing some research and the number of languages I will need to be able to handle is about 250. I will need to include their contact details (name, address, telephone, etc.) plus the languages that they speak (anything from one language to about ten languages per person). What is the best way to handle this from the following two perspectives –





1/ Database table setup. Do I need 250+ columns or do I allow the user to enter multiple languages in one field? I need to be able to search the database for people who can speak the required language or languages.



2/ Input form. What would be the best way for the user to select the languages they can speak (note: some users can speak multiple languages)?? Should I use checkboxes, radio buttons or drop down menu, etc considering the number of language names I need to display?



Any advice would be most appreciated.

Thank you.

Simon.
Quote Reply
Re: [jai] Database advice In reply to
A better way to handle this, IMHO, would be two tables: person (person_id, name, address, email, etc.), and language (language_id, person_id_fk, language_name). That way there's no redundancy or empty fields and you have all the flexibility (searching and indexing) of having one language per field. If you wanted to be really hardcore you could have three tables, one for person, one for languages, and one for the relation between the two. That would be the least redundant solution, I think, but could be a headache to work with in DBManSQL.

As for your second question, that's largely an aesthetic issue, isn't it? Clearly you don't want to allow text input, as people will write the same language in different ways, making it impossible to index properly. If there are 250 options, I would probably go for a select list, but checkboxes or radio buttons might work, too, depending on your site design.

Fractured Atlas :: Liberate the Artist
Services: Healthcare, Fiscal Sponsorship, Marketing, Education, The Emerging Artists Fund
Quote Reply
Re: [hennagaijin] Database advice In reply to
Hi,

Thanks for your advice. I still haven't got a clear understanding of the Relational Database way of thinking BUT I understand your suggestion and have already set everthing up. Works Fine!!

I opted for the select list as the input method.

Thanks again.

Simon.
Quote Reply
Re: [jai] Database advice In reply to
hi,

for the language-selection in the input-form I would use a MULTI SELECT field!

Regards,
Manu

Shopping Portal Shop-Netz.de® | Partnerprogramme | Flugreisen & Billigflüge | KESTERMEDIA e.K. | European Affiliate Marketing Forum.
Quote Reply
Re: [jai] Database advice In reply to
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