Large Tables: To split or not to split.

[PookaTech]

Hello everyone,

I am creating a database that will handle insurance quotes. There are two types of quotes, proposals and renewals. The quotes contain much of the same information: time tracking, group demographics, etc. Furthermore, renewals and prospects are both reported on in the same reports. Things like how many quotes we completed this month and what our average quote was. Yet, a proposal has 30-40 fields that a renewal does not and a renewal has 30-40 fields a proposal does not.

SO here is the question, should there be one table for the quotes with all of these fields, this means that every record is going to have null values for 30-40 fields, or should 3 tables be used: one for common information, one for prospect only info, and one for renewal only info. The 3 table design just seems wrong and I am thinking it could make for headaches working with forms. On the other hand the one big table would be easy to work with but somehow seemed cumbersome and wasteful of space.

I am not anticipatig more than 15,000 quotes in the next ten years. My concern is with performance and transparant design. File size is not too important although it seems to me that smaller file size is always a wonderful thing.

Thanks for any advise or input,
Pookatech

 

[jeremie_ingram]

What your getting at is normalization of the table structure. You would want to try breaking this one huge table into smaller related tables for several reasons. Considering that you say both entities have similiar fields, these would all be in one table. This table would contain the common fields, and would then be linked to the additional two tables (containing the 30-40 fields) via RELATIONSHIPS.

Ok, why do it then? First, it is considered "proper structure" and highly advised. It will ease the management of the system and reduce the overall size of the database.

 

[The_Doc_Man]

I would suggest splitting, with some research to define what is common between the record types, to result in three tables.

You want like things to be in a single table. So I would see

tblBaseQuote
lngQNumber (PK)
details...

tblNewQuote
lngQNumber (FK)
unique part for new quotes

tblOldQuote
lngQNumber (FK)
unique part for old quotes

This means that for a given quote, you will have a record in one table or the other, but not both at the same time. How you convert data from one to the other is, of course, your call and I won't second-guess you.

Also, if you want to look at every quote and want to see details on a common form even though some of the detailed parts may differ, you can look at a UNION query that joins the two subsets to the main table in two SELECT clauses. Look it up in the Help tables. It would be unique in only one sense: The two separate legs of the UNION query would be based on JOIN sub-queries, with one table in common and one table not in common with the other leg of the UNION. That doesn't happen THAT often.

 

[PookaTech]

Thanks

Hi Guys,

First, thanks for the replies. I should have asked the question a little more carefully, but the question is answered. Doc Man, your answer hit more to home.

It seems that everyone agrees that for a quoting event there should be the common table and two seperate tables. I will look into using a union query for the forms recordset. I have only used those for reports before; I wasn't aware they were updatable.

You can see that I would be interested I using a tab control for the prospect renewal info, but if it is just easier to have a seperate form for prospects and renewals, the that will probably be the end of it.

Once again thanks for the replies,
Pookatech

 

[PookaTech]

Rethinking Three tables

Hi Guys,

I am back after some thinking. Here is what I have. Let's pretend the world was going to end if I did not use a tab control for the prospect renewal info on my form. Therefore I need the prospect and renewal info in one recordset.

As you will see in the attached file, qryQuotes2Combined is a union query and unfortunately is not updatable. On the other hand qryQuotes uses two outer joins on the QuoteId, is updatable, and functions beautifully, EXCEPT for the fact that if prospect data is entered and then renewal data is entered the prospect foreign key is set to 0. Whoops. Why does it do that? I have no clue but it is unacceptable. That means if prospect information is entered and then a number is accidentally entered in the renewal section, the prospect records FK will be set to 0 and errors will attack.

I know I could cheat around this with a little vba. But cheating in access is rarely rewarding. I am wondering if you think that two forms is the only way to handle this well?

Thanks so much again,
Pookatech

P.S. I always normalize at least to the third form and when it is called for I go to the fifth. This has been challenging because I have not had to deal with a one to "possibly" one relationship before.

 

[The_Doc_Man]

That bit about the FK for prospects getting reset to 0 somehow means that the link is broken to the prospect when the repeating-customer link is actualized. I am wondering if there is a referential integrity issue here that normally ought NOT to be in use. But I cannot download anything to look at it due to my site's rather stringent firewall policies. (I'm on a U.S. military site.)

I'm afraid you'll have to rely on others for that problem. Can't say I've ever seen it before, but then you are doing something I've never had to do. My DBs have never worked with prospective anythings. Always after-the-fact, so I've never had to do that particular class of conversion.

 

[PookaTech]

Referential Integrity

Hey Doc Man,

I tried the operation with and without relationships and with and without referential integrity set. The idea would be the common table with two one to many joins linking the other tables to it. Of course the one to many's are both off of the primary key. It's a funny problem because it would have been so easy for microsoft not to screw this up, but they did.

BTW, I am using Access 97 because my department is stuck five years in the past. Nothing like Access 97 and windows NT yehaw!

See ya around,
Pookatech

 

[The_Doc_Man]

Ain't nothin' wrong with Ac97 that a good sledgehammer wouldn't fix. I'm a big fan of percussive adjustment. The old-fashined .45 caliber attitude adjustment tool works great, too!

However, to your problem. If it isn't a referential integrity problem, then I'm wondering what else would break the link. There is nothing in theory wrong with the idea that you could have records in each table corresponding to the same common record. In fact, as a consideration of historical data retention, you OUGHT to have that case. Therefore, I'm wondering at what step in your process the reference changes to zero.

If you can find that point and just not update the old record, you should have it sacked. I'm not sure what you are doing, but if all you are doing is creating a new record in the New table to go along with an existing record in the Old table, there is not one blessed thing wrong with the idea.

But I did note that the issue is based on an OUTER join, for which initially ALL fields in the secondary table are null. Also, a little bell is going off in my head that says that a UNION query will eliminate duplicate keys unless you tell it in some way to allow them. So if the Old and New "branches" of the UNION have the same key, one of them will have to go.

This MIGHT be a case where a second, separate linked sub-form would be in order, again linked between the primary key of the parent and the foreign key of the child form.

I'm hoping that someone else might chime in on this one. Pat, for example, has worked a lot with UNION queries whereas my experience, though good, is limited.

 

[PookaTech]

The Solution

Hey Doc Man,

Thanks for the reply. I restarted this post in the query forum when I saw that it was heading in that direction. Here is a link to that thread. http://www.access-programmers.co.uk/forums/showthread.php?p=306869#post306869. Pat answered that post just a while ago and now we have an answer.

The Keys in the prospect and renewal tables were defaulting to zero and that was causing the problem.

Thanks for your help,
Pookatech