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

[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

 

[Len Boorman]

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

Is this a DATA WAREHOUSE !

 

[Rich]

Is this a DATA WAREHOUSE !

err no, it's a Data Whorehouse

 

[Len Boorman]

err no, it's a Data Whorehouse

Similar smart ar**d comment that I cannot think of at the moment but sums up the thread subject

L

 

[Kevin_S]

That is why Kevin gave up because he knows his talk was all hobby bullshit.
Mike

Mike -

First of all - your one hell of a person to start calling people out when this WAS NOT EVEN A THREAD YOU STARTED. I gave up because YOU CONTINUE TO POST TO THIS FORUM AND RAILROAD OTHER PEOPLE'S THREADS!!!

After I "gave up'" I reposted a scaled down sample for monkeytunes to get an idea on the relationships for members and contracts... this had nothing to do with you but, as always, you start posting in someones thread, tell all the experts here their methods dont work, at least not in your cases, then continuing to argue for days and days about how your right and their wrong...

As for the "talk is hobby bullshit" speak - I guess if building relational database systems in SQL Server and DB2 with front ends ranging for Access 97-2002, VB6, VB.NET, and ASP for the past 6 years as a full time job and having an 2 degrees in the field make me a "hobbyist" then I guess your right... especially someone with your "qualifications"... I have tried REPEATEDLY to help you in your many posts but you are so far removed you do not see the forest through the trees.... Good Day and Good Luck as I will not be responding to your posts anymore...

Furthermore - some of us have jobs that require us to work during the day and we jump on this forum to try and help others when we have a free minute or two to try and return the help to others as we have received ourselves so just because I don't spam the board with posts like yourself does not mean I'm "not a real Access Person"

Heres a tip: Go to the Start menu -- click Programs -- Click Microsoft Office -- OPEN EXCELL AND HAVE A BALL!!!

Good Luck,
Kevin

 

[Rich]

Similar smart ar**d comment that I cannot think of at the moment but sums up the thread subject

L

the thread subject's fine, it's the idiot that keeps telling everyone else that they're wrong, who defiled the thread

 

[monkeytunes]

In a desperate effort to get this thread back on track (remember when it was a philosophical discussion of normalization? Way back when? Somewhere around post 50 and before?), I want to thank some people, and then I'm out of this thread, because this discussion has made for some strange bedfellows, and if I don't back out the door, before I know it I'll be trying to wrangle a herd of 300 macros.

Pat, your comments here opened my eyes as wide as dinner plates. Believe it or not, I HAD considered what would/will happen if my sups ask for, say, all components sent in a particular month. "Holy smokes" I thought, "I'll have to write 25 queries per month!" I had no idea, however, to the extent of just how out control it would all get. Thanks for the (extreme) wake up call. Come to think of it, every time you answer one of my silly posts, it's a wake up call. Who else around here has that feeling?

Ken, it will be a couple of days before I can try out your suggestions, but I've bookmarked it and it makes sense. Kevin_S, your comments and pointers are appreciated as well. I'm not looking forward to putting it into action (users get spooked when you tell them "the database will be down for a little while" and/or "there are going to be some changes"), but I'm looking forward even less to any ramifications that would crop up from keeping the databases this way. Thanks to you.



And Len Boorman:

To Normalise when designing a Relational Database Application is not a discussion point.

It is a fact.

No offense taken, but sometimes us gunslingers have to do unpretty things. Sometimes we have to rob trains, rustle cattle, build insurance databases with hundreds of 20 records tables and 300+ macros, and sometimes our supervisors at our REEELLY BEEEG companies tell us "You WILL make it look like THIS" and the only way to do it is "flatten" a table, and then we do the unpretty things we have to do. THAT, friend, is a fact. The trick, I'm finding out a little more every year (and I'm young yet), is knowing just what the hell you're getting into.

Ostracize me if you must, but I have a confession - I build at least two unnormalized databases EVERY WEEK. My excuse: the vice-president gets spreadsheets, needs to see "the big picture" quickly, like, within an hour, and so I just import the spreadsheets to seperate tables, relate common fields, do some spot maintenance, run a query or three, use some template forms I have laying around, and send it over to him for perusal/analysis. On one screen, he can see a project and who's involved, or one person and all their projects, or totals and everything else that you can't get from spreadsheets. I know the database won't be used for more than a couple days, at most. It is an unpretty thing I have to do, it bugs me knowing that I'm breaking rules (not to the point of losing sleep, but it bugs me), but in the interests of time and knowing that we'll be getting a brand new set of spreadsheets in a day or two that I'll have to reimport, taking the time to normalize just doesn't take precedence.

The databases I maintain that are in general continued use, however, I want done right. So, thanks everyone for the help. (And with that, I'll leave Mile, Rich and The Stoat to their haranguing of Mike.)

(And, who else thinks Aleb deserves some dirty looks for airing this dirty laundry in the first place?)

 

[KenHigg]

Cool. I'd like to see how it turns out...

Remember this invaluable golden rule when doing db: Make sure your "Tables have just the right number of attributes. (Not too many, not too few)"!

ken

 

[Len Boorman]

No offense taken, but sometimes us gunslingers have to do unpretty things. Sometimes we have to rob trains, rustle cattle, build insurance databases with hundreds of 20 records tables and 300+ macros, and sometimes our supervisors at our REEELLY BEEEG companies tell us "You WILL make it look like THIS" and the only way to do it is "flatten" a table, and then we do the unpretty things we have to do. THAT, friend, is a fact. The trick, I'm finding out a little more every year (and I'm young yet), is knowing just what the hell you're getting into.

Even us old codgers get to do a bit of rustling when he who pays the wages say "It will be done this way"

So no problems there.

But given the choice I think you and most would prefer to produce the best possible solution on the basis that we are ALL still learning and therefore what we produce will not be perfect.

Or is there somebody out there who knows it all ?. About Access that is. Nobody KNOWS what Rich looks like

 

[Rich]

Nobody KNOWS what Rich looks like

his family does

 

[Mike375]

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.

Mile,

Yes, that is correct. It would be like saying that the insured person's "card" has 100% of their details and 5% of the spouses details. In practice the amount of business done on the spouse is quite small and in probably 99% of cases the business is written at a later date.

If a policy is later written on the spouse then a macro makes another record and copies her details to the new record and then the bits relevant to her are added. The reason we add some basics such as date of birth of the spouse in the first place is for when we are talking to the insured on the phone. I have a rate calculator made which runs off the data on the form record that is open and two labels start it. One of the labels is geared for female rates and the spouse date of birth.

If the spouse has policy benefits then we also duplicate them with the second set being given the same ID as the husband. This is so her policy details will be seen in a list with his on a subform. In 99.9% of cases the husband owns the policies benefits that are written on his wife's life. Also, if the spouse has policy benefits written on her then although a "card" exists for her it is a secondary record and in the very large majority of cases it is excluded from mail lists etc.

Mike

 

[Pat Hartman]

Stoat -

If you look at my break down of your data you will see three address tables Business, Home and Postal. In your Client table you will have three fields to retain the PK of each one of the address tables.

-This is NOT a normalized structure. You are suggesting three tables when one would suffice. If someone's Home and Postal addresses were the same, they would need to be entered twice, defeating the purpose of the separate Address table.

Also adding three fk's, although not as bad as having three tables, is still not up to snuff. The person table has a many-to-many relationship with the address table. But, there is an additional piece of information that is needed to qualify that relationship. That missing piece is Role. This identifies how this address is used in its relationship with this person. So the same address can have more than one role with each person or have different roles with different people.

 

[aleb]

Hello all, yes it's me - incendiary
I have been watching the thread for the last few days without actually posting anything in it and I guess everyone will agree to the conclusions that :
1. Relational dbases are to be designed with the normalization rules as a prerequisite.
2. What level of normalization you will achieve depends on what is "enough" for developer.
3. Business needs/boss's vision and etc may overrule some or even all of the normalization rules, and access becomes nothing else but "automated spreadsheet" with some good features on top. Here I absolutely agree with Monkeytunes and his last post.
4. Access is just a tool which can make life easier. It is not worse of offending others for way they use access ... the other way can be suggested, reviewed, accepted or rejected.


So I am going to change the thread's name to "keep it normalized when it is possible"
Thank you all who posted their opinions.

 

[Len Boorman]

That missing piece is Role. This identifies how this address is used in its relationship with this person.

Can't you just rely on Pat.

Points out the failings precisely

Len

 

[Uncle Gizmo]

I’ve just spent half an hour reading this thread, because in another thread Rich was rude about another member. Someone challenged him on his rudeness, and he replied that the member he was being rude about was basically a disruptive influence, who wouldn’t listen to, or take on board any comments from other members.

Seeing this, I decided I’d better look through the offending person’s posts and makeup my own mind, that’s why I’m here reading this thread.

I now have a mental picture of this person (Name removed), he is sat at his desk, his boss comes along and says hi ****, I’ve got a bit of a problem with the data. I was wondering if you could produce a report for me with X. Y. Z.

Ah, well says ****, the thing is boss I can’t really do that report for you because you can’t do this sort of thing in Access, it’s not the tool for the job, you really need an SQL database, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah.

After about ten minutes his boss walks off, totally confused, and absolutely convinced that the only way he’s going to get his X. Y. Z. report is by spending thousands of pounds on new software. Obviously he hasn’t got the money available so **** is quite safe, he can carry on with his hobby of Access, without any need to learn anything new just operating it like a glorified macro driven spreadsheet.

Now, if you think I’m being rude, don’t forget I’ve just wasted half an hour reading through all this twaddle, just because one person won’t listen.

 

[Rich]

He's his own boss by the way and has argued with just about every one of the long term consistent posters on this forum. He's not the slightest interest in changing his db because he's flogging it off to some poor unsuspecting unfortunate in a couple of years anyway.

 

[Mike375]

Uncle Gizmo

You appear to be the type of person that needs the gov't to protect you from yourself.

Mike

 

[The_Doc_Man]

I posted early in this free-for-all and then got tapped to actually do some work here at the office. So I've been standing back a bit.

OK, here's the way I see it, and this is the old perfesser puttin' on his flat-head cap with the mildewed gold tassel...

To do this right REGARDLESS of the final form your design takes, you DO NOT START WITH THE DESIGN. Analyze several things. First, what data do you need? Write it down. Second, what kind of reports will you / your boss want to see? Write it down. Third, what kind of form will you / your data entry clerks want to see? Write it down. Fourth, what will your adjunct departments want to see? (By now, you know the drill.) Fifth, are there any laws governing what you keep and how your records must look based on these laws (usually related either to fair reporting or fiduciary responsibility issues)? Sixth, are there layout issues where you can take advantage of overlap, even at a low layer? Seventh, are there data elements that are truly optional at some predictable level of presence/absence? Eighth, are there issues in your published company policy that have any influence on how things are stored/visible? Ninth, does your company have a written style guide for offical company products / publications?

OK, you've looked at requirements. Now go back to your list. Each possible way of laying out your data for each different data user (or provider) might indicate different internal layouts for efficiency. But remember, Access imposes record-size limits. It also imposes other sizing limits. So now look at whether any record you want to build, whether in a table or part of a query, would exceed those limits.

The whole point of all the analysis is to see how you are going to use the data. This analysis tells you whether any of your requirements (and you surely will have more than one) indicate normalization.

OK, you've got several proposed layouts, perhaps because of forms and reports, perhaps also because of legal requirements, perhaps also because of company policy. EVENTUALLY, you'll reduce this to a set of tables (rarely one - but not NEVER - just hardly ever.... do I hear a chorus from H.M.S. Pinafore in the background???) The tables will be based on one or two central items that are at the heart and soul of your application. The other items will depend on details in the primary tables.

Now, you lay out your tables. You might have THOUGHT that it should all have been flat - but what about cases where a person has no dependents 'cause that person ain't married? So what you do is create SPARSE 1-to-1 tables for cases where something doesn't always exist. Then, you store the extended but optional data in the SPARSE 1-TO-1 table linked to the master table using PK/FK relationships.

Another thought: If you have information to be kept private while other parts are more "public" (perhaps a privacy law issue), split the tables in a 1-TO-1 manner and make the private data table have different security settings from the public table. Then if someone tries to get to the private data they can't, even though they can see the public data.

Now, the argument about "the boss wants it to look this way..." or "I can't place my fields on the form if I do it this way..." or other hoopla. That's all a cop-out for an infertile imagination, combined with ignorance of the REAL value of OUTER JOIN queries and/or INNER JOIN queries.

Guys-n-Gals, if you want to export a spreadsheet for the boss, build a query that outer-joins to the sparse table. Use the NZ function to "cleanse" the nulls that occur after an outer join. You can export queries - or directly display them - JUST LIKE TABLES. AND they look JUST LIKE SPREADSHEETS when you do!

You want to move fields around on a form? Build the form from a query. You can move the fields around JUST LIKE TABLE FIELDS if you wish. But the underlying tables can be very trim and compact. You can even do things with JOIN in the underlying tables. It really works! (Here, NZ would not always work, so this might be trickier. The form might have some extra data massaging to do...)

A QUERY is the way to give the appearance of denormalization without actually violating any rules. And who gives a tinker's DAM whether the boss wants a spreadsheet? (Yes, that's correct, not "DAMN" - look up the origin of the phrase.) In my personal experience, there is a learning curve in which the boss is happy - until the first day someone says, "But boss, we need to look at this a different way... can we sort it by X with breaks for every change in Y?" And on that day, a denormalized layout gets in the way of that different view of things.

Now, the business about Address1, Address2, Address3 etc. - as Pat pointed out - is NOT a repeating group 'cause these are actually components of a larger address field. Subdivision is not an excuse for separation. The fact that postal codes, city/state/county,etc can often be used for other purposes (routing or identification of local field offices or something similar) argues for field separation, yet the separated fields still depend on the same prime key. And you would not split out part of the address when the whole address is being used for its original intent.

Perhaps, to some viewpoints, this is a case in point for a LIMITED denormalization - where instead of having a separate table of address components, you fold all the components back into the table - but you would still keep them separate. And as it is a matter of interpretation, I leave that determination to the reader.

Finally, the whole thing, whatever you do, has to make sense in the context of your business. If your db doesn't reflect your business model to at least a fairly close match, you are engaging in hobbyist programming on company time. Oh well, I'll relent a little bit. That could also be exploratory research on design alternatives, maybe... But if your bad design lasts longer than necessary to realize that the model and the business have diverged, you are stealing time and money from your boss. If you are your own boss, fire yourself. If not, start reading the Help Wanted section fast.

If that sounds harsh, so be it. But that is the benefit of wisdom gained through almost 29 years of commercial and government programming.

 

[Kevin_S]

Well put Doc... great points on all accounts...

thanks for the post!
Kev