Normalization Denormalization of tables. Keep it denormalized when it is possible.

[Mile-O]

At the moment, I still believe that table meets normalisation because of the data in the different fields.

Do what you want!

 

[Rich]

In my opinion, any argument that would support splitting that table would also be an argument that says first name, surname, suburb etc should also be in separate tables.

Mike

look up "working with spreadsheets"

 

[Mike375]

Do what you want!

Well, how would you define a normalised table.

If the following is in fields:

First Name
Surname
Street
Suburb
Postcode
State

Is that acceptable in one table and if so..why.

Mike

 

[Rich]

Well, how would you define a normalised table.

If the following is in fields:

First Name
Surname
Street
Suburb
Postcode
State

Is that acceptable in one table and if so..why.

Mike

nope! postcode belongs in a separate look up table

 

[Mike375]

I don't understand why you would treat postcode differently to suburb, unless it is something to do with the counties we live in.

Mike

 

[Mile-O]

Well, how would you define a normalised table.

tblCustomers
CustomerID
Forename
Surname
Street
SuburbID
PostcodeID

tblPostCode
PostCodeID
PostCode
StateID

tblStates
StateID
State

tblSuburb
SuburbID
Suburb


I'm not too sure on the address details of Australia - but I'm guessing it's possible to have suburbs in different states with the same name. I don't, for example, know how an Australian postcode works or is defined.

 

[Rich]

you forgot telephone numbers and the Area code look up table Mile

 

[KenHigg]

Still not normalized.

 

[Rich]

Still not normalized.

define please

 

[Mile-O]

you forgot telephone numbers and the Area code look up table Mile

I only worked with the fields given.

 

[Rich]

I only worked with the fields given.

now come on, you could at least build the db for Mickey, he'd love to see how you'd do it

 

[Mike375]

Mile,

You could and do have suburbs with the same name in different states but the postcode is the separator.

In Australia the postcodes have numbers like 2078, 2098, 2209 for one state and then 3098, 3123 and then 4324, 4235 etc. In fact for mailing purposes the suburb does not count but suburb counts for "visual" purposes.

The phone number system is similar in that each of the states has its own prefix. I imagine it would be the same in other countries for both postcode and phone.

Mike

 

[KenHigg]

tblCustomers
CustomerID
ForenameID
SurnameID
StreetID
SuburbID
PostcodeID

tblForeNames
ForeNameID
ForeName

tblSurName
SurNameID
Surname

tblStreets
StreetID
StreetName

tblPostCode
PostCodeID
PostCode
StateID

tblStates
StateID
State

tblSuburb
SuburbID
Suburb

 

[Rich]

Ken, your Suburb isn't linked to PostCode

 

[KenHigg]

Yeah, I saw that after the post. My bad...

 

[Rich]

Don't you have a problem in The States with Zip areas overlapping though?

 

[Mike375]

you don't ask if the spouse has dangerous hobbies or is a smoker.

You do if she is the insured. In fact it will be asked as part of the application for insurance.

But if she is insured she will have here own record and her husband will form the spouse details on her record. If both husband and wife have policies then that makes a label visible which when clicked finds the other record.

Mike

 

[KenHigg]

Don't you have a problem in The States with Zip areas overlapping though?

Hum.. Not that I'm aware of... Could be though.

 

[Mile-O]

you don't ask if the spouse has dangerous hobbies or is a smoker.

You do if she is the insured. In fact it will be asked as part of the application for insurance.

But if she is insured she will have here own record and her husband will form the spouse details on her record. If both husband and wife have policies then that makes a label visible which when clicked finds the other record.

My suggested method still takes this into account. As the SpouseID field of the extension table would relate back to the primary key of the people table you can therefore pull over all her details and his details in a query.

From what you are saying you are duplicating data with respect to the spouse's date of birth (if both partners are covered) where you have the date of birth in the person's own record and the field of their spouse.

 

[Mike375]

How many is a few. 1000, 100000, 10000000? It doesn't really matter. To be fair this is a lookup table a 1 to 1 relationship - 1 client has 1 home address - what it does is give you control. Enter something like an address twice takes up twice the amount of room, gives you 2 opportunities to make mistakes and takes more than twice as long to change.

But the address is not entered twice. The spouse (and it can be the husband) only gets an address when they are insured. If one has the policy then the other's record is created from the first record (by one of thos evil macros ) and changes are made as required. For example, usually the address will be the same and if not it is changed.

Your form can be based on a query that displays all of the address with the rest of the clients details if you want it that way. It doesn't need to have subforms. Or you could have popup forms where you can display the address.

But are we not into a query that is going to pull the tables together and be back where we started??

You must be aware that when you phone up for services they go "Can i take your postcode please" and you say "cb2 2qq" and they say and what number is that and you say "1". They have done a search on their address tables and can now link you to the address. This takes 2-3 seconds for all the address in the UK 20 million + ( you can buy all the address on a cd and upload them into your database)- A system like yours would probably be well suited to an sqlserver with an Access front end.

We have a thing called Australia on Disk which sounds similar. However, 99.9999999999999999% of phone calls in this business are outgoing. The number of records on each computer that reside in the table in question is quite small, usually around 2500 to 4000. A holding table is where all our names for canvassing are entered and most of these are put in on a bulk basis. As calls are made and names are consumed the "processing" part of the data base brings in the required number of names from the holding table and appends the "rejects" to another table. As time moves along x% of names go to the reject table and y% stay in the table mentioned in this thread but become a client/policy holder, although that can be a process that takes several months. Thus many of the names spend a fair bit of time at the "half way" point.

Mike