I have two "entity" tables: Company and Agency. Both Company and Agency can have multiple addresses (shipping, billing, etc.) and multiple contacts with titles (president, sales rep, etc.).
Rather then put address and contact information in each entity table, I want to create a table that is common to both of them: An address table and a contact table. I looked into creating, for instance, an address table with a primary key (identity column), entity type (company or agency), entity key (primary key of the parent entity table) and address type (shipping, etc.), along with the necessary address information. I also looked into using a junction table holding the entity type, entity key, address type, and identifier from the address table. I'm having trouble with both approaches.
One problem I'm having is with foreign key constraints. If I have a foreign key from the parent entity table related to either an address row or junction table row, I found that I have a foreign key violation when I try to insert data. If I try to insert Company information, the Agency foreign key constraint is violated. What I guess I didn't fully understand is the two way nature of the constraint: If I add a child row for Company, the Agency says I can't have a child without a matching parent Agency row.
How can I get around this? I'd like to use one table for addresses and one for contacts, but I can't add a child for one parent without having a related row on the other parent. Of course, I'd like to keep using the foreign keys for referential integrity, but do I have to resort to getting rid of the foreign keys and use triggers instead?
By the way, I'm doing all this on Sql Server 2005.