"Common" tables

[jasscat]

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.

[pclfw]

I have used this style of table reuse before and always handled the releationships in the application.

As you say if you try to set foreign key relationships from either the COMPANY or CONTACT table you will always get a key constraint violation when entering a record into either of these tables.

However your appluication can handle the relationship constraints as well as the address 'type' rules (i.e. can a Contact have a SHIPPING address? maybe yes; maybe no.)

[jasscat]

I ended up taking the easy route. In my stored procedure, I ended up first disabling the foreign key constraint check that was causing me problems with:

ALTER TABLE address NOCHECK CONSTRAINT FK_AGENCY_TO_address;

I then do my insert into the table. Next, I reinstate the foreign key constraint check I disabled:

ALTER TABLE address CHECK CONSTRAINT FK_AGENCY_TO_address;

So I can keep using the foreign key constraints for referential integrity. Maybe not the ultimate solution, but it works for my purposes.