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

[The Stoat]

And in response to Pat's data warehouse(DWh) post. This is exactly what they do in the Hospitals i have worked at in order to allow information departments access to the Patient Administration System (PAS) data. More often than not we would have a SQLSERVER DWh that pulls all of the PAS tables into the SQLSERVER at midnight. The following day we can run reports using cubes or access. If necessary we can do a manual pull to get a fresh look at the current PAS data but it takes ~1 hour. Often their will be a waiting list table or an activity table which has been created using a massive append query linking many tables and running all sorts of subs. It simply wouldn't be feasable to pull this data together from the individual tables every time you wanted to look at the current waiting list.

TS

 

[Mike375]

Kevin,

Most of the life companies maintain a client register which is on Access and is supplied to the agent via their quote system. Their client table looks very similar to what I use.

The One to Many part of the deal occurs with policy benefits as Bill Bloggs might have 17 different benefits.

"Capturing" the data is not you are outlining. Initially all names that are entered in the "macro driven spreadsheet " are done in bulk and they come in as prospects. At that point the information is limited. They are then canvassed and either go onto be sale or not. The sale on a medical specialist will commonly be over a 3 to 6 month period and the data comes in bits and pieces.

Mike

 

[KenHigg]

Kev - I guess that would work...

I was just hung up on how one would model it, not know waht the heck it was supposed to represent; a policy or a policy holder. Seems like you would need a policy->primary holder data and a policy->secondary holder data type structure...

ken

 

[Kevin_S]

potentially but who really knows for sure...? I do not have the background with the data so I was trying to think of a model based on the fields provided - and, for the sake of this forum topic, keep it as basic as possible. Basically just trying to illustrate there are other ways around it...

Kev

 

[Mike375]

Kevin,

This the key point...from your post:

as the data becomes available it is added to the database without having to store blank/null data in the first place.

For me, the fact that a lot of fields will be Null does not matter.

With your system of all these tables will I need subforms. Using a query to join the stuff back up is a waste of time but you said you would do it another way.

Mike

 

[monkeytunes]

What exactly is a component? Is it a paragraph or a clause that the customer agrees to comply with but which may take some time to fulfill after the contract has been signed? Say, like your company requires they prove insurance by suppling you with some type paperwork?

It's all those things. But, honestly, my department, is only concerned with dates. The legal department can handle all the other crap. All we care about is when we get a copy of each component in our files. I'm not going to do the legal department's tracking for them!

If this is so, What happens if your company is ready to do business but they are not in compliance with a clause?

Hahaha, I didn't want to have to put this out there...but my company is REEELLY BEEEG. We have clout. I'm 100% sure you've heard of us (<hint>location</hint>), but to protect my own continued employment, I will not name names. If they are not in compliance, then we tell them "Well, it's been pleasant, best of luck in your future endeavors", and there are 7 more business waiting to take their place to get in the door with us. Seriously.

When the entire contract is in compliance?

I have queries and coding set-up to show us completed contracts in the full DB. This isn't my issue. My issue is: what about the example I posted?

Finally - Wouldn't you guess that down the line some where, your legal department would:

a. Want to keep track of old, out of date contracts, and
b. Change the contents, revise the components of new contracts.

a: Their problem, not mine. They have a whole massive department to deal with it; my department is concerned with what we need. That's why we're a seperate department, after all!

b: It happens. That's why they keep me around to maintain stuff. Nontheless, even when the legal department makes changes, we still just track dates of when we recieve stuff. The legal department can handle content; we just want our checklists checked off so we can do what we need to do. (And, to be honest, we're DISCOURAGED from getting involved with that stuff - this is a BEEEG place, and duties are divied up for a reason, given to specialized departments. It's a hive!)

I told you in the post above that you will never have null fields in this case because you DO NOT ADD the records in until the data is available...

And I told you that I have users (temps) who can't HANDLE putting in the data unless it is laid out for them like in my example. So what I have now is: every time we put in a new project (1 record), we have 25 null fields that fill up over time. What a normalized version would give me is 25 new records, with one field with lots of nulls that will full up over time.

In the normalized set-up, sure, we might have many, many less fields, but what I've been doing to accomodate the users is using an append query to add 25 records per project as if this was a survey, like Pat Hartman and The Doc Man pointed out in this thread , because ALL components will eventually be required.

If someone, anyone, could point out how a normalized version of my example would work in the real world with my skittish users, where all the fields need to be laid out like they are in my example, without the users having to put in anything other than a date, <lie>I'll eat this paper-clip, right here. (No, really, there's one here. I'll totally eat it. No, really.</lie>)

Believe me, I'd rather have this thing done right, hence, all my questions...the last thing I need is denormalized chaos and hundreds of macros stinking the place up.</snarky, unsolicited shot at Mike375>

 

[Kevin_S]

Mike - yes subforms are the way to go

monkey - I'm sure you could retrain your users based on a normalized structure as having a suborm with two fields is not that difficult. If they really could not handle it I assume there are a thousand people lining up to take their place in your massive, all powerful, all controling, place of business.... right...?

either way - I give up as I have work to be done and can not waste time on this thread any longer so I wish you the best of luck on your endeavors...

<Actually I just freeing up time to denormalized our entire HR database and turn it into a 50,000 field spreadsheet as this seems like the way to go now a days... >

Kev

 

[The Stoat]

TS

This is where we hit the area of the business's needs.

For example, qualifications is only one entry and looks like FRACS or FRACP etc. The first one is Fellow of the Royal Australian College of Surgeons and the second is College of Physicians. Other qualification don't has those sort things tell us that he is a surgeon or physician specialist. If he is a surgeon then Total and Permanent Disability based on the inability to perform his own occupation will not be available with many insurance companies. Thus it is as important as date of birth etc.

Things such as assets, liabilities and workers compensation are the type information that we must have for gov't compliance reasons. Assets and liabbilities are just one figure with no other information. Well that is quite true as that sort of data is held in file notes which forms a Many to One back to the table in question.

Much of the other data "profiles" the person in terms of insurance and types of policies, premium rates and so on.

Mike

Mike, the importance is not actually important. The data isn't lost/ of less value because it is in another table. The positioning of fields in a table is down to the relationship between the object the table represents and the attributes of that object.

For example a private car hire firm with drivers. We would like to hold data on a clients cars and journeys.

So TBLCAR;
CARID(unique autonumber not a PK component)

MakePK
ModelPK
Number of doors PK
Engine size PK
Gears PK
petrol consumption (MPG)
MAX number of passengers

We can now make instances of this car in a new table called TBLNEWCARS

NEWCARID (unique autonumber not a PK component)

REGISTRATION NUMBER PK
CARIDFK PK

Easy a brand new car and all we needed was a registration number. I can make as many as i like by adding only two bits of data together a whole fleet of Ladas -happy happy joy joy -

How about a journey?

TBLJourney
JOURNEYID (unique autonumber not a PK component)
NEWCARIDFK PK
DATEOFJOURNEY PK
TIMEOFJOURNEY PK
petrol cost per litre
Miles travelled
DriverIDFK ( we always need a driver to make a journey driver is an attribute of the object journey)

Passengers are attributes of a journey but we never now how many we will have so it must be at least 1 (business rule).- it can only be the max number the car can carry (business rule).

TBLPASSENGERSONJOURNEY
PASSENGERSONJOURNEYID (unique autonumber not a PK component)
JOURNEYIDFK PK
CLIENTIDFK PK

TBLCLIENT(unique autonumber not a PK component)
CLIENTID
FNAME
SNAME
ADDRESS1
ADDRESS2
TOWN
CITY
COUNTY
STATE
POSTCODE
DOB
NATIONAL INSURANCE NUMBER (SOCIAL SECURITY NUMBER?) PK
PHONE
E-MAIL

We add passengers to the journey and check each time that we are not trying to add to many.
I can find out all the information about the passernger from the TBLCLIENT but i need only hold his unique ID in the TBLPASSENGERSONJOURNEY I can find out all the journeys they have been on by looking for his CLIENTID as CLIENTIDFK in TBLPASSENGERSONJOURNEY and linking that back to TBLJourney using JOURNEYIDFK and JOURNEYID

No data is lost, No data is less important the difference is i can hold a large amount of data together using the smallest amount of data possible. It makes searching quicker I can find all the journeys my client has been on simply by filtering on one field in the TBLCLIENT that lets say only holds 100 records. Potential their maybe tens of thousands of TBLPASSENGERSONJOURNEY records but my client has been on only 1. The Jet engine will do this so much faster than searching all those TBLPASSENGERSONJOURNEY records. I can work out the cost per trip. I don't need to hold the information it can be worked out from TBLJourney, Cost per litre, Miles travelled and TBLCAR petrol consumption (MPG). This obeys the 3rd NF rule that you don't hold calculated data.


I can't make it any clearer than that i'm afraid.

HTH

TS

 

[Kevin_S]

Stoat - save your breath - Mike will argue he's right until he's blue in the face... if you want proof search his name in the forum - you'll see many examples...

good luck I'm out!

Kev

 

[Mike375]

Kevin,

Subforms are the problem as you simply can't position fields on the forms as you can with fields that are part of the data source.

Now of course with policy benefits there is no choice but they are separate anyway.

The fields of I have in that table are not "multi" to the person. In other words if I currently have 10 records then if I split the table I will finish with 10 records in each table. That to me would be like putting home address, business address and postal address in three different tables.

Mike

 

[The Stoat]

Stoat - save your breath - Mike will argue he's right until he's blue in the face... if you want proof search his name in the forum - you'll see many examples...

good luck I'm out!

Kev

Funny i know my example isn't perfect in every detail but when i started it would have taken me all week to figure that out. I quite enjoyed it. So sad where's that gun??

TS

 

[Mike375]

TS

The problem is subforms.

The Journeys in the car are not the same as what is in my table. That would equate to

Policy benefits
File Notes
Diary appointments
Calls made.
etc.

If I split the table in four then I am stuck with subforms and the problems of laying out the main form being used. If I join the tables back, what is the point.

Mike

 

[Mike375]

Stoat - save your breath - Mike will argue he's right until he's blue in the face... if you want proof search his name in the forum - you'll see many examples...

good luck I'm out!

Kev

Kevin,

Some of you blokes toss this stuff around but you have already said the subforms have to be used.

Well, you tell me how I set out the main form and position the fields where I want then and also have the flexibility of Visible/Invisible and MoveSize running OnCurrent.

Gains Vs Losses

What do I gain if I split the table. I know what I lose.

Mike

 

[monkeytunes]

monkey - I'm sure you could retrain your users based on a normalized structure as having a suborm with two fields is not that difficult. If they really could not handle it I assume there are a thousand people lining up to take their place in your massive, all powerful, all controling, place of business.... right...?

True. You'd be surprised how fast we burn through those temps. Which is why I need to keep it as simple as a spreadsheet.

I'm not trying to argue that my table should be denormalized - I WANT to do this right. In my example, I even put an extra joined table in, doing EXACTLY what you said, Kev. What I need to know is HOW do I set the data entry form up, using continuous forms or whatever, so it looks like what I put in my example? This is where the real-world comes crashing in on me; fields are super-easy to arrange on a form, and if you look at my example, certain fields are different colors and placed in certain places...so I did it that way. Arranging specific records on a subform based on critera or what-have-you, well...then I'm kinda lost.

But you say you're giving up, when what I've done repeatedly is ask for help on a simple example that I've already posted. I WANT this normalized, but I DON'T KNOW how to get the results I need, and because I need to get a paycheck every week, I did what I had to do and built the thing denormalized for this one table, and it's been giving the results we need for 3 months. But I WANT to go back and do it right, and issuing the "normalize it!" platitude (which I promise I try to follow! PROMISE!) isn't what I need; this monkey needs someone to look at his example and criticize it. (And where else am I going to go, Utter Access? Not a chance!)

The only person who appears to have taken the time to look at my example was uber-guru Pat (for which I'm grateful), and she said she couldn't tell because I only included a form, no data, so I reposted the example with dummy data et al. Should I start a new thread with "Newbie Normalization Help TeH RoX0rz!!1!"?

My point: The thread started with denormalization theories, someone said "Let's see an example of denormalization in use", I posted one with the general gist of "this is what's getting me by, but I'd sure like to know the RIGHT way to do it if this isn't kosher", and nobody will look at it.

HELP ME.

(Man, this feels like it's getting heated, and I REALLY REALLY REALLY didn't want that to happen. Where's the smiley for "head in my hands"?)

EDIT - Here's a link to my older post with my example...

 

[Mike375]

]This is where the real-world comes crashing in on me; fields are super-easy to arrange on a form, and if you look at my example, certain fields are different colors and placed in certain places...so I did it that way. Arranging specific records on a subform based on critera or what-have-you, well...then I'm kinda lost.

This is what I want to know as well

But I have a bigger problem than you because the main form is used for telemarketing and the layout of those fields is vital.

Mike

 

[Rich]

Funny i know my example isn't perfect in every detail but when i started it would have taken me all week to figure that out. I quite enjoyed it. So sad where's that gun??

TS

ah yes but, Mickey's been at it since 1995

 

[KenHigg]

Is this more acurate than the first one:

1. All customers/vendors will have a contract.
2. A customer/vendor may have more than one contract
2. Contracts are broken down into components.
3. All components will be approved or completed before any business transaction occurs.
4. A component approvel will always have a reference to an external identifier('App #1, Final Sig, etc.),a date attrribute and may have a textual addribute. ($ amount or comment)
5. A new contract will have a standard set of components that it will be modeled after.
6. The components that will make up a contract will never change once the contract has been signed.
7. The new contract component model may change, with old components being removed and new components being added
8. Once a contact has expired, the contract information can be discarded.

???
Ken

 

[monkeytunes]

Is this more acurate than the first one:

1. All customers/vendors will have a contract.

Right.

2. A customer/vendor may have more than one contract

No. I fear I've been a bit misleading. Each company/vendor can have many contracts, but each PROJECT THEY WORK ON has a complete contract. Again, the example in my database lays it out nicely. You'll see I put in two companies and 40 some-odd projects/contracts, with 20-some-odd components to each project/contract.

2. Contracts are broken down into components.

Right. My department just tracks the 20-some-odd components we care about.

3. All components will be approved or completed before any business transaction occurs.

Umm...that's a question for Accounts Payable. All we care about is "When do we have copies of these 25 things on file?" on a component by component basis. It's REALLY that simple.

4. A component approvel will always have a reference to an external identifier('App #1, Final Sig, etc.),a date attrribute and may have a textual addribute. ($ amount or comment)

Right on.

5. A new contract will have a standard set of components that it will be modeled after.

Right (I think). Each new one will have these 25 things we track.

6. The components that will make up a contract will never change once the contract has been signed.

Right.

7. The new contract component model may change, with old components being removed and new components being added

Possible (as is anything), but it hasn't happened yet. So that's a theoretical "right".

8. Once a contact has expired, the contract information can be discarded.

Right.

I'm sold on the normalized table with a subform, I'm in, goose and gander, hook line sinker, I'm in. What nobody can tell me is...how to arrange the normalized RECORDS on a continuous view subform - the way I've arranged my denormalized FIELDS on a form? (Apparently Mike375 is looking for this info too...and if someone tells him, maybe his labyrinthian macro monster might be on the way out?)

And, hey, Ken, dude, thanks for sticking with this. This thread is quickly getting completey out of control, and I appreciate your tenacity.

 

[Rich]

I'm sold on the normalized table with a subform, I'm in, goose and gander, hook line sinker, I'm in. What nobody can tell me is...how to arrange the normalized RECORDS on a continuous view subform - the way I've arranged my denormalized FIELDS on a form? (Apparently Mike375 is looking for this info too...and if someone tells him, maybe his labyrinthian macro monster might be on the way out?)

either use unbound controls and code or look at one of the flexigrids advertised here. If you just want to view the data you can of course use a crosstab query

oh and by the way, Mike isn't looking for anything, he hasn't the time or the inclination to change anything

 

[Kevin_S]

monkey -

wasn't trying to abandon ya earlier it just gets frustrating trying to deal with some who post continuously with one opinion without considering the benefits of the other...

Here is an example I threw together based loosely on your topic area (contracts). I wanted to show you from a visual standpoint how (1) - this can be accomplished using a subform that is pretty basic and (2) how you don't append all 25 components to the new contract - you simply (in this example) select the component from the pick list as it is available and add the records in a many to one relationship as they become available...

Note* - I didn't know what the names of these 25 components are so I named them Component 1, Component 2, Component 3, etc... - If you would like to change these you can do so in the table tblComponent...

HTH and post back if you need more assistance...

Kev