Truckers and Insurance database design help needed...

[Dave Lambert]

Hi everyone!



The team I’m on deals with trucking companies and their insurance providers. We currently have our information in a couple spreadsheets but I’m trying to convert this information into an Access database. It’s giving me fits for the following reasons:

A trucking company can have coverage with more than one provider.

A provider can have multiple offices in multiple states.

An office can have more than one contact person. The contact person can be an agent, administrative assistant, etc.

As an example:
Insurance company ABC
Insurance company DEF
Insurance company GHI
Insurance company DEF has 7 offices in 4 states.
A given trucking company deals with agent 3 in office 5.

At this point I have three tables designed: Carriers (truckers), Producers (insurance) and States.

How do I get this into third normal form so when I need to deal with updates I get the correct contact person/agent in that agency in that insurance company?

Any help is greatly appreciated!

Oh, and by the way...Happy Thanksgiving!

[Micron]

You will need more than 3 tables. What I'm seeing is

- tblInsCo (Insurace Companies as a 1 side table; that is, no dupes)
- tblInsCoLoc (many locations to 1 insurance company; i.e. repeating CO ID's per location)
- tblLocConts (many contacts to 1 insurance location; i.e. repeating LOC ID's per contact)
- tblTruckCo (Trucking Companies as a 1 side table; no dupes)
- tblTruckInsCo (many Insurance Companies to 1 Trucking Company; i.e. repeating Truck Co ID's per Ins Co.)

EDIT
Forgot to mention that if I have this right, it would be the bare minimum. You might want lookup tables to control input. An example would be lookups for companies so that user cannot arbitrarily add their own or misspell a company name when creating a new association between that company and an additional location or contact. Don't confuse my mention of lookup tables with lookup fields. They are not the same thing.

[ArviLaanemets]

Must given truck company deal with only certain agents/contacts of certain insurance companies?

When yes, then you need assign agents to truck companies. Insurance companies and their locations will be properties of agent.

tblLocations: LocationID, Location, ...;
tbllInsComp: InsCompID, InsCompName, ...;
tblInsCompLoc: InsCompLocID, InsCompID, LocationID, ...;
tblInsAgents: InsAgentID, insCompLocID, ForeName, LastName, ...;

tblTruckComp: TruckCompID, TruckCompName, ...;
tblTruckCompInsAgents: TruckCompInsAgentID, TruckCompID, InsAgentID

In case you need to have the information, which truck in truck company does have the insurance from which insurance agent, you also need tables like
tblTrucks: TruckID, TruckCompID, ...
tblInsurances: InsuranceID, TruckID, InsAgentID, ...

[Dave Lambert]

Thanks for the responses!

The trucking companies I'm dealing with are mostly independents. We do deal with a few of the larger companies though.

ArviLaanemets, I have a question; I'm not understanding something completely. What is the tblLocations used for? The tblInsCompLoc contains the insurance company/agency location information. At least that's the way I currently have the locations accounted for. I may be wrong though.

Can I paste a snippet of a spreadsheet in which I'm doing the basic design of the tables? Some of the tables will have more fields than what the snippet will show. That way you can see what I'm thinking with my design.

[orange]

Dave,

I'm curious. Why do you have a table Producers when you identified them as insurance Provider?
Are you interested in individual Policy(ies)?
Can you lead us in simple terms what you do with the collected data?
That is, what does this mean with some details?

The team I’m on deals with trucking companies and their insurance providers.

You may get some ideas from the free, generic data models at Barry Williams' site.

Here's one that may give some insight.

[Dave Lambert]

Orange,

I'm on the Logistics team of the company I'm on site at. The company is an agricultural company; their products consist of seeds, etc.

The Certificate of Liability Insurance typically states "Producer" and then the insurance company/agency/agent fills their contact information into that portion of the form. The term "Producer" is a bit generic but it is what we call the insurance company/agency/agent. The Certificate is basically proof of insurance.

We are not interested in particular policies because many of the Producers are independent agencies or work for an independent agency because they can involve many actual insurance companies. The Certificate of Liability Insurance can show multiple actual insurance companies that actually underwrite the coverage. I've seen more than 4 insurance companies shown on one Certificate of Liability Insurance form. I'm glad I don't have to get to that level of detail!

We keep this information because the company I am on site at requires the truckers/trucking companies (we call them carriers) to have a contract with the company. The company requires specific types and amounts of coverage before the carrier can haul for them. If any coverage expires or the carrier does not have the minimum required amounts that carrier can be blocked from hauling for the company. Each coverage can expire on a different date. Some of the Producers do not send updated Certificates when a coverage expires or renews. We track this so we can contact the Producer or someone at the carrier to let them know that a coverage has expired or is close to expiring.

I hope I explained things well enough. If not please let me know and I'll try to do a better job of explaining things.

[rpeare]

Just throwing another opinion in there.

You just need one table for all of your companies with an indicator as to type:

tblCompanyType
CT_ID  CT_Description
1      Trucking
2      Insurance

tblCompany
C_ID  CT_ID  C_Name ----->  Other company related fields
1     1      Trucking Company A
2     1      Trucking Company B
3     2      Insurance Company A

Similarly one table for people with an indicator

tblPeople
P_ID  CT_ID  C_ID  P_Firstname  P_LastName  C_ID  ----> other person related fields
1     1      1      Trucky       McTruckerson
2     1      1      Drivey       McDriverson
3     2      2      Insurey      McInsurerson

Storing the CT id in the people table wouldn't strictly be necessary but it might make queries etc a bit more efficient.
Then you need to address locations and people associated with each location as mentioned in prior posts, my approach to that would depend highly on how frequently people move between locations, or employers for that matter. You have a better idea than any off us how often that happens, but if it's relatively frequent you'd want a relatively easy way to transfer a person around locations without allowing them to be in multiple locations, but perhaps they can be in multiple locations as well, i.e. an insurance agent working out of multiple offices.

[orange]

Thanks for the info Dave. I recall a post from the past on similar subject, but can't find it at the moment.
I did find this link re Certificate of liability Insurance.

Update: Found it. Seems it was an earlier thread by you. https://www.accessforums.net/showthread.php?t=73507

[Dave Lambert]

I've updated my design from 3 tables to 6 tables. I think I have the relationships correct but that may be part of the problem. I can't get things to work. I'm not in IT so I don't work with Access very often. I've been going through some tutorials on how to accomplish things in Access. I'm so frustrated I'm about ready to redo everything and use one main table and say to heck with the data duplication. Yes, I know that's pitifully poor database design but I can't get a correct design to work.

[orange]

Dave,

The tutorials from RogersAccessLibrary in the material at this link Database Planning and Design info can help with design.
They all follow a process which can be used with any database. I have attached a copy extracted from the Rogers material.

It requires a description of your "business". That part you have to provide, but you are in the best position to know what you are trying to automate. The process will get you to a design. And you can test that design with pencil and paper. Adjust and repeat as necessary and the result will be a blue print for your database.

Too often, too many people jump to physical database without a solid design.

The other articles/info in the linked material complement the process.

Good luck.

[Micron]

I've updated my design from 3 tables to 6 tables. I think I have the relationships correct but that may be part of the problem. I can't get things to work. I'm not in IT so I don't work with Access very often. I've been going through some tutorials on how to accomplish things in Access. I'm so frustrated I'm about ready to redo everything and use one main table and say to heck with the data duplication. Yes, I know that's pitifully poor database design but I can't get a correct design to work.

maybe post a pic of the relationships if you've created them. Or a zipped copy of your compacted db. Putting everything in 1 table isn't just about going with an un-normalized db with lots of redundant data; it can make data handling/processing more difficult. If you've created lookup tables and made them part of the relationships, they really don't belong there. That may not be your issue though.