I am having an issue with determining how I should create table relationships. Here are the four tables I have (shown with abbreviations and only the relevant fields)...

Company
--------------------
- CompId

User
--------------------
- UserId
- CompId

Address
--------------------
- AddId

CompAddLink
--------------------
- CompAddLinkID
- CompID
- AddID

Now my issue is that a user can only have a relationship with an address if the company has a relationship with the address. Here are things to consider:
1. If a CompAddLink is deleted, it should cascade

2. A CompAddLink can not be updated if a user relationship exists

So here are the options I am pondering:
1. Create a link table between User and Address. This would require:
> Update trigger on CompAddLink to prevent updates if a user is using this relationship
> Insert/Update trigger on UserAddLink to validate that a user has rights to this address

2. Create a link table between User and CompAddLink. Table would look like this:
UserCompAddLink
--------------------
- UserCompAddLinkId
- UserId
- CompId
- AddId
> Create the following relationships:
a. UserCompAddLink.CompId to CompAddLink.CompId
b. UserCompAddLink.AddId to CompAddLink.AddId
(meets needs but now I am duplicating data in my database and I try to avoid this)

3. Create a link table between User and CompAddLink. Table looks like this:
UserCompAddLink
--------------------
- UserCompAddLinkId
- UserId
- CompAddLinkId
> Update trigger on CompAddLink to prevent updates if a user is using this relationship

Now one common item in all three scenarios above is that I believe I will need is the use of a Delete trigger on CompAddLink to remove user relationships when a company looses rights to an address. To me scenario 1 seems the cleanest but utilizes 2 triggers when only 1 may be needed (using scenario 3). But I have never used a linking table to create a relationship to another linking table. It just seems wrong.

Any help on this would be appreciated. Thanks in advance.

How about this:

You have an AddressID field for both Company and User. You also have a flag field on the User to indicate that their address is linked to the Company address. Then, your business logic can take care of the rest. If the flag is set, anytime the company address changes, the user address will change with it. If it is not set, then a duplicate address should be stored to keep any changes in the company addresss independent of changes in the User address.

This would also allow your User to add supplemental information (department, cubicle/office #) to the Company address when they are being created to make things more relevant from the beginning.

Let me add on to what I just said... So your design would really look like this:

User
-------
UserID
AddressID
CompanyID
CompAddrLinkFlg

Company
--------
CompanyID
AddressID

Address
--------
AddressID


You wouldn't need any other tables for this.

Use this approach and it will solve all your problems. Use Party table for anything, User, Company... I would use Related Party if any company is related to other company e.g. subsidiary...

Enjoy

Thanks Ciperlad

But each Company can "own" more than one address and each User can relate to one or more of them. So the AddressId in the Company and User tables does not solve that. And I do like the idea of allowing the User to add more information (I will have to remember that for my next project like this) but the actual purpose of this is assigning shipping addresses to a company. Then the sales manager has the ability to assign shipping locations to the sales users. Each salesperson is only allowed to ship from certain addresses.

So I need to relate multiple Addresses to a Company and then allow a User to be related to any of the addresses that the Company relates to. My big question is how to keep the relationships valid from a DB level. I know I can enforce this using code, but that demoralizes part of my DB.

PazPower,
I appreciate your response as well, but again it only solves part of the problem. I need to make sure the tables can not have data that is not valid. And using your diagram I do not see how applying that to my problem would prevent the link tables from storing "bad" data.

I think you can solve this problem using a compound key - it's not as neat as it could be, but it's simpler than most of the other suggestions.

Company
---------
*CompanyId

CompanyAddress
----------------
*CompanyId
*AddressId
AddressDetails

User
-------
*UserId
CompanyId
AddressId

(* denotes Primary Key fields)

So the User table uses both CompanyId and AddressId as its foreign key to the CompanyAddress table, and you can create a constraint on that. This prevents a User from having an address that isn't valid for their company.

Is that the problem you wanted to solve, or have I missed an important requirement?