Is this database designed okay?

[pg13Reader]

Hello you helpful Access designers!
I’ve been reading a lot here on the forums and have learned some very useful information, but sometimes you need someone to look over the work and make sure it’s going on the right track. A little background…I worked with Access 95 & 97 for ten years, mainly with queries & reports, not much in database design. I have learned all about data normalization, and hopefully have applied it successfully. I’m now working with Access 2007 (still a newbie) and attempting to build an insurance tracking system. I haven’t created a DB like this before so I’m hoping to have an experienced person take a look (see attached) at my tables & relationships to make sure I have a good table foundation, before I start in with the goodies (forms, etc.).
Thank you!!!!!

[June7]

Table t_Addresses has such specific information, think I would change it to t_Contacts and include fields:
ContactID (instead of AddressID)
ContactName
ContactCompany

Then the ContactID could be saved directly to the ClientCODE, UndwtrCODE, BrokerCODE fields of t_Policy. Or have a junction table:
t_PolicyContacts
ContactID
PolicyID
ContactType (Client, Undwtr, Broker)

Then tables with details specific to each type would relate to t_Contacts.

PolicyLength could be calculated, not saved value.

You have UndwtrCODE in t_Brokers but no defined relationship. Why would there be?

People name parts should be in separate fields (LastName, FirstName, MI, Suffix, CourtesyTitle).

[pg13Reader]

Thanks June7 for your analysis. A couple of follow-up questions...
Is your suggestion to blend together the three tables (t_Clients, t_Underwriter, t_Broker) into the one Contacts table? I was wondering if I needed a linking table instead of placing all of the “links” into the t_Policy table as it now stands.

If I separate people name parts, which seems like a good idea, how would you handle spouses, especially those with different last names? With more fields (SpouseFirstName, SpouseLastName) and have null values for clients without a spouse or same last name?

The answer to "You have UndwtrCODE in t_Brokers but no defined relationship. Why would there be?" is that not all brokers are assigned to a specific Underwriter, some brokers work with many different companies, and I wanted that field only filled in if a broker is tied directly to one underwriting company. In this scenario would you still link the two tables by the field, but not enforce referential integrity? Course, if I merge it all into one table then that link wouldn't exist....

Many thanks!!

[CraigDolphin]

if you can have one broker to many underwriter companies, and many brokers can be linked to one underwriter, then what you need is a junction table to handle the many-to-many relationship.

tblBrokers
BrokerID (auto, pk)
BrokerFirstName
BrokerLastName
etc

tblUnderwriters
UnderwriterID (auto, pk)
UnderwriterName
etc

tblUnderwriterBrokers
UnderwriterBrokerID (auto,pk)
UnderwriterID (fk)
BrokerID (fk)

[June7]

I view the primary entities of your db as People and Policies. People have attributes (name, address, etc) and Policies have attributes (type, cost, etc). Then these entities have a relationship.

What if some contacts could fit more than one type (broker buys policy from you and is therefore also a client)? This means same name in multiple tables. What if it is misspelled in one?

With so many specific fields in Addresses I don't see how an address record could apply to more than one person. How often will multiple persons have the exact same email or phone number. These alone make the record uniquely associated to one person. My suggestion is to put all contacts in one table and have child tables for any data that would be unique to a contact type. Such as the special fields for Clients. Then either join Contacts to each of the contact fields in t_Policy or have a junction table.

Having all contacts in one table can facilitate some processes, such as sending a general communication to all (notification of your new mailing address, or sending Xmas card as for instance). Also, searching for a name is simplified.

How far to take data normalization is a balancing act between ease of data entry and ease of data output.

As for spouses, yes could have fields in Contacts and tolerate all the empty fields or have a child table for family members. You already have some fields that won't always have data (StreetAddr2, website, phone2).

[pg13Reader]

Thanks for the replies! This has been helpful, giving me a way to see the relationships in a different way. I'll have to try the modifications out tomorrow.