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

[Mile-O]

Also, why would include DateOfBirth in the little table when all of the other things equally (actually more so) influence premium and policy availability.

Because, based on my extension table idea, the only thing which you store related to all people is their name and date of birth. The address of course can be dealt with either way.

As I'm saying a people table can hold all customers and spouses and the only stuff you store of both these people is their name and date of birth - you don't ask if the spouse has dangerous hobbies or is a smoker.

Also, why would include DateOfBirth in the little table when all of the other things equally (actually more so) influence premium and policy availability.

Because I'm thinking in data storage terms and you're thinking in a procedural/insurance operational manner.

 

[Mike375]

In some instances I would use a subform but it wouldn't be in datasheet mode - it would in be in Single Form mode (not continuous) so that it looks as if its part of the parent form.

The problem is still field positioning and lack of flexibility for visible/invisible/movesize running OnCurrent.

Now if there was gain....then let's here what the gain would be.

As far as I can see if I have 10 records in that table and have other tables because I split that table then each table will finish with 10 records each. Although I think Kevin S's suggestion would mean that some of the tables would have less than 10 records. But that would be a problem because we would not know if the person did not have a solitor or accountant etc.

At the moment, I still believe that table meets normalisation because of the data in the different fields. There is not one field that was listed that could equate to Hobby1 and Hobby2.

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

 

[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.