Need another Opinion (Database Design)

I need some other opinions on whether or not this is considered a proper database design structure.

Here is the relationship...We have PEOPLE, that each can belong to a COMPANY.

PERSON_TABLE
Person_ID
Company_ID

COMPANY_TABLE
Company_ID

Then each person can trust other people of other companies, but can only trust 1 person per company.

My question is this. In order to maintain a constraint of 1 person per company, is it considered OK to add a the redundant column Company_ID to the PERSON_TRUSTED_TABLE(and then creating a composite primary key on the Person_ID/Company_ID) instead of just adding a trigger to the PERSON_TRUSTED_TABLE to uphold the constraint.

PERSON_TRUSTED_TABLE
Person_ID
Trusted_Company_ID
Trusted_Person_ID

I would appreciate anyone's opinion. Thanks so much!

There is a third option although it's not much different from your PK idea. I think the major questions is in how many other tables do you reference the person_truested_table? You need a PK in your table anyway, so if you don't create a composite PK out of (person_id, trusted_company_id), what would you do to create a PK?

1.) would you create a PK column?

In this case you could create a unique key on (person_id, trusted_company_id), which has the same effect as the PK and you don't need a trigger. This would be my personal preference if you reference this table by many others, simply because you have a 1 column PK.

2.) would you create the PK out of (person_id, truested_person_id)?

Well this in not much different from the (person_id, trusted_company_id) idea, so i would add the trusted_company_id column and create the PK on those as you suggested.

What i would not do at all for this problem is to create a trigger.|||

As I understand your issue, it seems that it is necessary to allow all any and all [Person] to trust any and all [Company] -constrained that any one person can ONLY trust one person from any one company.

If that seems about right, then a constraining composite key (PK or UI) in the [Person_Trusted] table is what is needed. That composite key 'should' be on [Person_ID] and [Trusted_Company_ID].

One person, Bill, ID: 25, is allowed to trust only one person for company Acme Industries, ID: 1001. It doens't matter which Acme Industries employee Bill has trusted, once he has a trust relationship with Acme Industires another cannot be allowed unless he first removes the existing relationship. Or he can just UPDATE the [Person_Trusted] table to reflect the replacement 'trusted' person.

As a side note: You don't need the archaic denotation of [_TABLE] on the table names. It will be obvious from the context that [Person], or [Company], or whatever is in fact, a table. And it is wasted keystrokes to have to continually type something that does not provide any value.

|||I understand what you are saying about the _TABLE thing. I just put it in the post to be more descriptive.

I get your point, but do you realize that you can get to the Trusted_Company_ID from the Trusted_Person_ID. So really, by putting the Trusted_Company_ID on the PERSON_TRUSTED_TABLE, it would be redundant data.

Instead of putting the Trusted_Company_ID on the PERSON_TRUSTED_TABLE, you could just create a trigger on that table that gets the Company_ID from the Trusted_Person_ID, and upholds the constraint. Therefore no redundant data.

After this explanation, do you still think that adding the redundant column Trusted_Company_ID to the PERSON_TRUSTED_TABLE is the correct implementation?

Thank you so much for the input. It is very helpful.|||

In my opinion, this is one of those worthy exceptions to 'normalized' data.

Yes, it is a 'little' redundent. However, anytime you manage the Trusted_Person, you will most likely have the Trusted_Company information at hand -so it is not a 'big deal'. Consider establishing [CASCADE UPDATES] and/or [CASCADE DELETES] as appropriate.

My consideration is that using a TRIGGER is a relatively heavy handed approach, adding significant effort to the server to manage what should be a 'simple' CONSTRAINT.

The effect you are seeking is that for any one Person there can be only one Company trusted (it doens't matter who the Trusted_Person is for that Company). Having to traverse through the Trusted_Person-Company to determine if there is already a record for that Person-Company could be unnecessary effort.