I have a long loan application that I am modeling.
The form has a collateral section that I know I should break out into a seperate table.
Each record will have Property Description, Model Number, and value for example.
But what about Phone numbers. Is it OK to have homephone, workphone, cellphone or should you have another table. PhoneNumber, PhoneLocation? Of course then you probably should add an AllowedPhoneLocation table.
What about MothersName, MothersPhone, FathersName, FathersPhone. Or should I have a Relatives Table with Name, Relationship.
Does either of these solutions break any of the 5 laws?
If not how do you choose which way to go?

On a related topic.
Should Pick tables be created for even simple values or coded into the combo box select option? For example: Sex: Male, Female. Relationship: Mother, Father, Friend, Other. Or Marital Status: Married, Single, Divorced.

What are the advantages or disadvantages of each of these options? On what criteria do you base your choice.

Has the teacher covered any of these issues in class? What comments did they make?

-PatP

That is very funny. After rereading my post I can see where you got the idea it was a homework assignment. Actually I’m closer to a senile old man. The last concert I went to was Harry Chapin. My question was rather simple minded in phraseology but the query was honest. I know the “Is it OK to have homephone, workphone, cellphone or should you have another table (PhoneNumber, PhoneLocation)” question doesn’t much matter.
The advice I would give myself would be that if you have one or two phone numbers, leave them in the master table. If you plan on having a bunch and can’t define the prompt in advance for example “Vacation Home Phone” then separate them into a table.
I am thinking about a new project after many years of not thinking about this stuff. I remember the old days years ago when I would I would trade newsgroup posts with Date and Pascal (http://www.dbdebunk.com/page/page/616965.htm). Pascal’s books were very hard to read I remember. So I thought it couldn’t help to go back to the basics.
I did get a useful suggestion from another forum. He suggested that for all the simple fields that need validation to use a combined lookup table. Fields would be (ID, FieldName, PossibleLookupValue). Simple enough, used it before, but forgot about it.
So if you have any thoughts I’ll be happy to hear them and you will not be contributing to the delinquency of a minor.

The question I must always answer (as the type of person likely to model to the infinite degree if left unchecked) is this: What will the resultant system be used for, who will use it? how good are they with this kind of thing? How much data goes in, how much out. In short the question is: Do I, stricktly speaking, have to bother?

When the important stuff like what can I / should I leave out is done then the "fun stuff" of what can I do and how much can I show off?...

I hope these thoughts are informative.

Other thoughts:

Should the calateral have a depresiasion/value increase field use negative numbers for one or the other.

When you do Male/Female you can use a boolean or Yes/No (True/False On/Off) field just decide which is which first.

When you say pick table I hope you relate by primay key?

One other thing sometimes when designing a form you can get quick creative.

I had a form where the user would type details as they typed the field would disply the most likely end word(s) this is called type ahead or predictive typeing. The control was a combo-box or drop down which ment that values used before were now available to use this time. There was no relationship just the row source was "SELECT [fieldname] from table GROUP By [fieldname]" which gives all the different entries once only.

-Matt

Darn the bad luck! Here I was hoping that I could contribute to the delinquency of a member of the Swedish Bikini Team!

Your original query sounded so much like an entry level data modeling assignment that I was pretty sure that it wasn't a good idea to reply. Every once in a while I get surprised, but I see something like 1000 homework assignments for every legitimate request when they have that sound to them. I'm always willing to give students a hand, but I refuse to do their homework for them outright.

On a bit more constructive note, we more or less beat this issue to death in a recent thread. We managed to prove that for any group of N dbas, there are approximately N * Log(N) opinions on how to normalize, and there are more than one defendable positions on what constitutes normalized.

In your example I would really suggest going to a child table to list the phone numbers associated with a given parent row. Some rows might not have a phone number, others might have a dozen (I'm a really bad offender in the phone number area). If you want, I can probably post a graphic that would show this better.

-PatP