Design Review: Junction jable linking three tables

[justgeig]

Attached below is a snapshot of part of the design of a database I'm working on. Just wanted to get some more eyes looking at it and seeing potential problems.





I've set up the tables this way because we do business with both individuals and companies. These individuals are often also part of a company we do business with. Sometimes the mailing address is the same between a company and an individual, sometimes it is not. Also, we maintain and use many different mailing lists. These mailing lists are to be targeted to both contacts and companies as well as different mailing addresses that each company or contact may have.

Example: John Smith owns Smith Enterprises which is a provider of pet supplies. John also is a subscriber to a magazine we publish but Smith Enterprises is not. John also sits on the board of Community Foundation which is an organization focused on providing assistance in becoming a home owner. In this scenario, we will use our mailing list table to send pet related mail to John Smith at Smith Enterprises but not at his home. Another mailing may target John Smith as an individual as well as John Smith as a board member of Community Foundation.

So, what say you all? Good design? Bad design? Things that could be better?

[June7]

Don't understand the MailingLists table. Why does it have CompanyID?

[justgeig]

OOPS! Forgot to link it.

We also manage other companies' mailing lists and do their mailings for them as a service so we want to link the mailing list to a company.

[June7]

I suspected that. A company could be a client you manage a list for as well as a recipient of other mailing?

[Dal Jeanis]

Looks like the Companies table information is denormalized (Phones, addresses, email etc). Less so for Contacts, but still some.

[justgeig]

I suspected that. A company could be a client you manage a list for as well as a recipient of other mailing?

Correct.

Looks like the Companies table information is denormalized (Phones, addresses, email etc). Less so for Contacts, but still some.

What would you suggest doing to fix it?

[justgeig]

How about a ContactInfo table and a PhysicalAddress table?

[Dal Jeanis]

The design's not bad. The proper level of normalization depends on usage. If you want your contacts to be able to have multiple email phones/extensions, then you might pull them out into a different table. Which usually means you'll create a phone-type table so you know what kind of phone it is. Ditto with email addresses. Ditto with Web Addresses.

But only do that change if it makes sense to you, based upon how you know the application and data will be used.

With regard to the companies table, decide whether you want the address to be in its own place, in which case you can leave it as is, or move to a PhysicalAddress table, or whether you want it merged in with the similar data in the Mailing Address table. I can't think of a good reason why it shouldn't be merged, but you might be able to.

Designer, know thy data.

[justgeig]

The design's not bad. The proper level of normalization depends on usage. If you want your contacts to be able to have multiple email phones/extensions, then you might pull them out into a different table. Which usually means you'll create a phone-type table so you know what kind of phone it is. Ditto with email addresses. Ditto with Web Addresses.

But only do that change if it makes sense to you, based upon how you know the application and data will be used.

With regard to the companies table, decide whether you want the address to be in its own place, in which case you can leave it as is, or move to a PhysicalAddress table, or whether you want it merged in with the similar data in the Mailing Address table. I can't think of a good reason why it shouldn't be merged, but you might be able to.

Designer, know thy data.

Very well said, thank you. I had never considered moving the phone data out into it's own tables. I keep finding myself looking at table structure from an end user's point of view and then having to correct myself. For this database, it makes a lot of sense to merge the mailing addresses and physical addresses into a table and then having a address type table. Thanks again for your input!

[Dal Jeanis]

No prob. There's always tension between normalizing the data and optimizing the application.

All gripes and quibbles aside, technology is so effective nowadays that the business flow, your data usage, and the knowledge base of the staff (ie you) are the overriding concerns in the design of an Access application.

FYI - It's actually a good practice to start out looking at the data and app from the user's point of view, then improve on it. If you start from a techie POV, you're likely to miss limitations and requirements that are obvious from the user's side of the fence.

[June7]

There's always tension between normalizing the data and optimizing the application.

Tension like a high wire act - balance between normalization and easy of data entry/output. At some point you might say, "Enough! I am happy with 3 phone number fields in my table even if not every field will be filled for each record, I can tolerate some blanks." Issue can arise if you want to search for a number that could be within any of the 3 fields although it can be handled.

[Dal Jeanis]

Yep. Clearly, if you want to allow searching by phone number, then you want the phone numbers in a single column of a single table, if at all possible. Understanding the business requirements and the work flow, and guessing the future directions of mission creep, are critical factors in good design.

[justgeig]

I'm working on updating the table designs. I'll upload a new image when it's done and see what you guys think :-)