normalization/junction table question

[scroce]

I'm putting together an insurance database. There are two tables within this db that i'm specifically concerned with right now. One is tblCustomers and the other is tblCoverages.

Data in tblCustomer tables are names - Joe, Fred, Bill Bob.
Data in tblCoverages are things like- Auto, Homeowners, Flood.... etc.

One customer can be associated with many coverages, and one coverage can be associated with many customers - therefore I need a junction table - right? so now I've set up this junction table and the data looks like this:

Customer Coverage
-------- ---------
Joe Auto
Fred Auto
Fred Flood
Bill Homeowners
Bill Flood
Bob Auto
Bob Homeowners
Sally Flood

It's easy to to something like:
Select Customers with Auto - that would return Joe, Fred, and Bob.

But what if I wanted to do something like:
Select Customer with ONLY Auto - which would theoretically only return Joe,

OR

something like:
Select Customers who have both Flood and Homeowners Policies - which would return Bill but skip Sally.

I don't think I can do this can I? I think I messed something up - the only other way I can think of doing it is having a yes/no column for each coverage with a table and then querying on the value of these fields.

But that seems like a bad way to do it, since ultimately, this database will have 15 or 20 different coverages of insurance - it seems like it should have it's own table.

Anyone offer any assistance?

thanks!
Q: Why is my computer doing that?
A: Random Perversity of Inanimate
Objects

 

[JeremyNYC]

Hopefully your lookup table actually contains numerical IDs for people and coverages, not the names themselves. If not, fix it and then just pretend you did it that way just for illustration, but really there are two number fields, not two text fields <G>.

The first problem query could be solved with:
QryIncludeTheseCompound
==
SELECT tblCompanyServiceLU.CompanyID, tblCompanyServiceLU.ServiceID
FROM (SELECT tblCompanyServiceLU.CompanyID, tblCompanyServiceLU.ServiceID
FROM tblCompanyServiceLU
WHERE (((tblCompanyServiceLU.ServiceID)<>1))) as qryExcludeThese
RIGHT JOIN tblCompanyServiceLU ON qryExcludeThese.CompanyID = tblCompanyServiceLU.CompanyID
WHERE (((tblCompanyServiceLU.ServiceID)=1) AND ((qryExcludeThese.CompanyID) Is Null));


This is a compound query, that is first I made a query called qryExcludeThese, which included everything that wasn't ServiceID = 1, then I made one that joined that and the lookup table, with null on the query side. Then I just grabbed the sql of the first one and put in in place of the query name here. You'll of course have to change all the names and paramterize (in some way) the ID you're looking to have.

[Rereading my explanation, it looked a little hard to understand. It will be easier if I break out the two queries. It works eitehr way, I just like the one-query method more.]
QryExcludeThese
==
SELECT tblCompanyServiceLU.CompanyID, tblCompanyServiceLU.ServiceID
FROM tblCompanyServiceLU
WHERE (((tblCompanyServiceLU.ServiceID)<>1));

QryIncludeThese
==
SELECT tblCompanyServiceLU.CompanyID, tblCompanyServiceLU.ServiceID
FROM qryExcludeThese RIGHT JOIN tblCompanyServiceLU ON qryExcludeThese.CompanyID = tblCompanyServiceLU.CompanyID
WHERE (((tblCompanyServiceLU.ServiceID)=1) AND ((qryExcludeThese.CompanyID) Is Null));

Certainly the next one could by done dynamically in code with UNION queries, but there's probably an easier way.

Hopefully this helps.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developers' section of the site for some helpful fundamentals.

 

[scroce]

thanks for your response - i'm going to study it and then post back with what happened, but i did want to write a quick response that yes, I am using numerical ID's for everything involved - I was trying to be clear and concise in my posting. Q: Why is my computer doing that?
A: Random Perversity of Inanimate
Objects

 

[scroce]

another thought on this one - do you think that i'm even doing the right thing by setting up the junction table here-

maybe I should just go with yes/no columns - or maybe I should be doing something that i'm missing something altogether.

the SQL looks pretty intimidating, and this is only using two or three types of insurance - what happens if we add like 5 or 6 more - the compound SQL could become unmanageable - the users are going to want to see the data eight ways from tuesday. Q: Why is my computer doing that?
A: Random Perversity of Inanimate
Objects

 

[JeremyNYC]

It's a tough call. I know the SQL is intimidating, but if you spend some time with it (first get comfy with the two separate queries), it will become clear. I alwasy TRY to keep things normalized to the greatest extent possible. There are times, though, when querying becomes a nightmare with fully normalized data, and sanity is a valuable commodity. Do keep in mind, though, that what you save in work on the queries, you'll have to put back into the application in code to ensure that your denormalized data are accurate. And if you go with a bunch of fields in the Customer table, you will have a hell of a time when (not if) the client decides to carry a new kind of insurance.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developers' section of the site for some helpful fundamentals.

 

[scroce]

yeah - that's exactly how I envisioned it too. I'm glad to see that you have written exactly what I was thinking. At least now I have some confirmation that I'm on the right path of reasoning.

I guess this could this be one of those situations that is going to be messy any way you cut it - Q: Why is my computer doing that?
A: Random Perversity of Inanimate
Objects