Partially Duplicated Info

[QuantifyRisk]

I (beginner) am trying to find the most efficient way to set up this DB (planning stage). I'll try to explain what is going on in the DB to give some background on the situation.



I'm setting up a DB that holds information on agencies. Mailing address, agency code, contact info, etc. Every agency has at least one producer. Every producer has a license in at least one state. For example:
Agency: Dumbo
Producer: Pooh
State: KY
License: 5
State: NJ
License: 61

Producer: Scooby
State: OK
License: 34

So there is no constant number of producers or states that they work in. I could have one producer in 5 states, or one state. I could have 10 producers ranging from 1 to 6 states.

I would like to break it up into 2 DB. One for agency information, one for producer info (linked together of course). Obviously, I could have it enter each state on its own line but that seems like a lot of duplicate info. There's also more information that I need (SS#, appointment date, etc) but I'll briefly explain here:

Producer SS# State License
Joe 5-6-7 PA 5
Joe 1-4-4 NY 87

Can anyone help me make this a little more efficient? Thanks.

[QuantifyRisk]

Looking back now, I feel this should be in Database Design. Could a mod please relocate? Thank you.

[rpeare]

You would actually need a minimum of 5 tables.

A table for your agencies (with an autonumber PK)
A table for your contacts (producers, with an autonumber PK field)
A table for your states (just a list of 2 characters with an autonumber PK field is fine unless you are tracking state specific information as well)
A junction table connecting agencies to producers (again with an autonumber PK, with a FK to your agencies and a FK to your producers)
A junction table connecting producers to the states in which they have a license. This table would contain the license numbers for the producers. (Autonumber PK, FK to producers, FK to states tables)

[QuantifyRisk]

Why would I autonumber the PK and not use the agency/producer # which is unique?

[June7]

Number fields are supposed to provide better performance for sorting and query joins because number fields build indexes faster.

I often use a custom unique identifier for PK/FK. I have not experienced noticeable performance issues but my databases aren't all that big.

[QuantifyRisk]

This DB may be large. It's presently info that's stored in a few excel files. One of those files is 31 MB. This is a for a large insurance company.

[June7]

My biggest db is split design. The frontend is 40MB. The backend is 178MB. I have only one table set with autonumber field as PK and that was to avoid a compound PK.

[QuantifyRisk]

I know the agency code is a 10 digit number, but I don't know about the producer codes. I don't even know how they vary from state to state.

[June7]

What is this database for? What agencies? What producers? What do states have to do with the codes?

[QuantifyRisk]

DB of insurance writers. Insurance agencies have "producers" which are people licensed to write insurance in a certain state. However, they may be licensed in other states and not all producers write in the same states. So an agency may have 2 producers that each write in two states but not the same states. The FB is meant to keep track of their license information.

[rpeare]

The reason I prefer autonumber PK's is that they are not subject to data entry, and for someone new to database design, it's the easiest way to create a primary key. Let me give you an example.

Let's say you Jim Smith comes in and he gets a provider number 1234567890
You set him up in an agency and possibly even enter his licensing information.

Now a couple of days later you realize there's been an error and his provider number is actually 1234567891. If you go in and change his provider number all the data that is related to him via his provider number has just been orphaned (basically meaning there is no valid link to any other table).

Like june7, I've got plenty of tables that have a constructed PK but they are harder to handle for a neophyte. I never, ever, ever allow data entry to provide the primary key of a table, it's just too easy for users to foul it up.

[QuantifyRisk]

Good point. I won't even be using the table. 4 women in their 50's will be.

[June7]

Isn't Cascade Update supposed to handle the revised PK to automatically fix child records? Of course, that requires relationship to be set in the Relationships window.

rpeare is correct about the risks with custom identifier. I don't allow the identifier to be entered by users. It is automatically created and data integrity managed by VBA code. This identifier is in the form YYYYA-XXXX and the sequence starts over every year. This is very different from your agency identifier which is apparently assigned by some governing body, like SSN assigned by the IRS. And SSN should not be used a PK/FK.

[QuantifyRisk]

You would actually need a minimum of 5 tables.

A table for your agencies (with an autonumber PK)
A table for your contacts (producers, with an autonumber PK field)
A table for your states (just a list of 2 characters with an autonumber PK field is fine unless you are tracking state specific information as well)
A junction table connecting agencies to producers (again with an autonumber PK, with a FK to your agencies and a FK to your producers)
A junction table connecting producers to the states in which they have a license. This table would contain the license numbers for the producers. (Autonumber PK, FK to producers, FK to states tables)

I linked the producers to the state table with the junction Producer2State License table I created. However, they are linked by their auto-numbered primary keys. (Enforce Referential Integrity rule)

[rpeare]

If you're talking about creating relationships I don't use those at all. I find them annoying

The first three tables will have an autonumber PK.

The junction tables will have an autonumber PK that is UNRELATED to the first three tables. but each table will carry a pair of FOREIGN KEYS (FK) that link to two of the other tables in this type of construct.