I am collecting data to put into a db from application forms and I am not sure as how to design my tables. Example questions are:
Do you have a car? yes no. IF yes, what model
If my understanding is correct I will have empty records for people who dont have a car. Does that mean I have a table called CARS that would have a 'model' attribute and some kind of primary key linking the person to the car.
What confuses me is that the table seems to be created dynamically. If a person has a car, create a new car instance etc.
Another which baffles me is:
What is the society's main source of income? Please tick relevant and estimate its percentage of your total income. Options:
Government funding (TICK) Percentage :
Public donation (TICK) Percentage :
Self--funded (TICK) Percentage:
Does that mean in my schema I create a table for each option to store whether its ticked, and the percentage, and a society_num(PK) for the relationship to the society?
That would mean that a society_num would be duplicated as many times as options selected are selected? Am I thinking in the right direction or am I completely wrong?
Also what is the best approach for having multiple addresses or telephone numbers in a contact table for a single person when you dont know how many addresses or numbers that person may have.
Any help would greatly be appreciated!
Last edited by grooverinthesouth; 03-17-06 at 10:35.
Most of the items you mention sound like they could be handled in a flat table and, yes, there will be null fields ( or percentages set to 0 if the associated true/false item is "false). The addresses and phone numbers, however, might work best in a separate table, with a M:1 link to the primary table. This is because the number of entries is uncertain. If it were in the primary table, you wouldn't know how many fields to allot.