Normalization - associating addresses with either contacts orand companies

[Historypaul]

I'm building the core contact management portion of my database and I'm finding that almost always, addresses will be related to companies, but occasionally, an address will be associated only with the contact - not the company. Since the data of the addresses is the same (street address, city, state, zip), I created one address table, one company table and one contacts table, then I created many-to-many tables linking the contacts to their addresses and the companies to their addresses.



This just makes working with the data on forms difficult.

Would I be better off just adding two linked fields in the address table so that each record could be linked to a contact or a company? Or should I have two address tables identical with one linked to companies and the other linked to contacts? It's really two individual one to many relationships. One-to-many contacts to address and one-to-many companies to addresses.

[ItsMe]

The approach I use is to have a tblCustomers and a tblContacts. Any location that is ever responsible for paying a bill gets a record in tblCustomers. So if a company has a field office in LA that is responsible for paying its bills and another field office in New York, NY responsible for theirs, that company gets two records in tblCustomers.

All of the contacts go into tblContacts. If the example company has a third field office in Albany, NY, I will not add that address to tblCustomers unless they have their own credit app on file and Accounting knows what to do with their client ID. I will simply include it in tblContacts and duplicate the address in tblContacts (each contact at the field office has a record where their address’ literal text is stored).

If business rules were to change or someone complained, I would employ a Junction table or additional tables for tblHeadquarters and tblFieldOffices. The business rules would dictate what FK fields would be in the new tables.

[Historypaul]

Thanks.

Oddly enough, it felt like I was answering my own question as I was writing it. What I have needs (2) separate one to many relationship tables, so that's what I'm going to shift everything towards. It seemed unnatural that I would have two identically structured tables, but they are technically each for different purposes and while I will have redundant table structures, at least I won't have redundant stored data.

[ItsMe]

It does boil down to the business rules. I have other tables with similar structure to my tblContacts. These other tables have different relationships than a contact record would though. One example of a need for such a table would be for locations that are relative to a Job or a customer’s order that are NOT contacts. However, this will not guarantee non-duplicated data.

Consider the creation of tables to support tblContacts and or tblCustomers that does not have the same data structure. How would you employ a combobox or listbox in a data entry form that was used to append tblContacts or tblCustomers? Answering these questions will help you to define the structure of supporting tables. The combo’s Rowsource would depend on another table to retrieve a Key value to be stored in tblContacts or tblCustomers.

This approach is what avoids duplication of data. Use FK’s in the supporting tables to explain how the child tables are related to the parent tables. There may be several FK fields required in the child tables in order to support the business rules.

[ButtonMoon]

This is a very common situtation. Take a look at the Party Data Model:
http://www.tdan.com/view-articles/5014/

This just makes working with the data on forms difficult.

Sound database design has nothing to do with the design of forms. Build the right data model, then build the forms you need to populate it.

[Historypaul]

I ended up having two separate tables - one for companies, one for individuals and having an address table for each. I wish I had read the article that ButtonMoon had posted prior to setting that up. The data model presented in the article he posted puts a lot better perspective on it - effectively stating that both people and companies are "parties", and how the parties can relate to their locations. The biggest change in mindset after reading that article was the idea of using the Surname of the contact for the company name and a separate many-to-many relationships table to connect companies with individuals at that company... It's a beautiful thing, but I've already designed a bunch of forms and hard coded a ton of stuff and I'm not redoing it unfortunately.

Thanks for the answer, ButtonMoon!