Hi again, everybody.
I trying to design another database, and I could use some help.

My client, an insurance agency, needs a system to keep track of their insurance agents, each agent's license status in each state and carrier, license status, by carrier and state, etc.

I am not good at describing things, so let me give some examples:

• James Hefield has applied for an insurance licenese in Maryland with KyleGroup, and his status is pending - he applied on 01/10/2006.
• James has also applied for, on 12/18/2006, and been approved for, on 01/08/2006, an insurance license with MetalMilitia. His insurance license expires on 06/10/2007.
• Any variation, by AgentName, Status (There are different status levels depending if they're currently not licensed (have applied but are waiting), or if they are licensed (have been approved.))

Originally, the client gave me a list of reports which she'd need to be able to pull up. I asked for details, and they are listed as follows:

• All Licenses by Expiration Date [Michelle Schoff] user's name, state, license status, license number, expiration date
• All Licenses by State [Michelle Schoff] user's name, license status, license number, expiration date, SSN, DOB, address, email
• All Licenses by Individual [Michelle Schoff] user's name, state, license status, license number, expiration date, SSN, DOB, address, email
• All Carrier Appointments by Carrier [Michelle Schoff] user's name, state, license number, license status, expiration date, appointment status,
• All Carrier Appointments by State [Michelle Schoff] user's name, carrier, license number, license status, expiration date, appointment status
• All Carrier Appointments by Individual [Michelle Schoff] user's name, carrier, state, license number, expiration date, appointment status, SSN, DOB, address, email

(carrier = insurance company that we're trying to get a license from)

They also told me that License Status and Appointment Status are different - here is what they gave me:

License Status:

• Active
• Pending
• Terminated

Appointment Status:

• Appointed
• Pending Appointment
• Pending Deletion
• Deleted

I know this is a lot, and I'd normally be able to do this on my own, but I haven't delt with a layout as complex as this one before.
I'm looking for someone who could help me with the layout of a database for keeping track of the above.
Any help is very much appreciated!

--Kyle Johnson

In my experience, if you don't know how to structure the database, you need to understand the business process better.

Questions:
- Is the "appointment" a test-like session with some sort of pass/fail? (Do you need to track multiple attempts to qualify for the one license?)
- Does the license expire and can be renewed, or is a new one needed, with (perhaps) a new "appointment"?