How to enforce a constraint over multiple tables?

Question

I have the following five tables:

• PRODUCT
• FEATURE
• PROVIDER
• PROVIDER_PRODUCT
• PROVIDER_FEATURE

The logic is as follows:

A generic PRODUCT can have one or more FEATUREs. There are different PROVIDERs who are delivering the same product, but with different details, and the same FEATUREs, also with different details.

The problem is that with this ERD I can create a PRODUCT p1 with FEATURE f1 and a PRODUCT p2 with FEATURE f2. Then I create PROVIDER pv1. Now I can create a PROVIDER_PRODUCT pvp1 with PROVIDER pv1 and PRODUCT p1. But when I create a PROVIDER_FEATURE pvp1 I should only be allowed to add FEATURE f1, as this is the FEATURE that is linked to p1 via the PROVIDER_PRODUCT table. However I can also add a FEATURE f2.

How can I create a constraint that prevents the user from entering a PROVIDER_FEATURE that is part of a FEATURE that is part of a PRODUCT but that is not the PRODUCT on the PROVIDER_PRODUCT that is on that PROVIDER_FEATURE? Do I need to solve that in a stored procedure, or is there a more elegant way to enforce this?

+-------------------+       +--------------------+
|                   |       |                    |
| PRODUCT           +-----> | FEATURE            |
|                   |       |                    |
+---------+---------+       +----------+---------+
          |                            |          
          |                            |          
          v                            v          

+-------------------+       +--------------------+
|                   |       |                    |
| PROVIDER_PRODUCT  +-----> | PROVIDER_FEATURE   |
|                   |       |                    |
+-------------------+       +--------------------+

          ^                                       
          |                                       
          |                                       
+---------+---------+                             
|                   |                             
| PROVIDER          |                             
|                   |                             
+-------------------+                             

Answer 1

This can easily be done by using cascading keys that overlap. Here is an example using the Oracle Data Modeler (note there is a bug in this tool or a configuration issue as the Provider_Feature table should show each column as PF meaning both PK and FK):

In this example, the PK to Provider Product includes the Product Number provided, and the PK to Feature includes the Feature Number supported for that Product. The Product Number in Provider_Feature is a FK back to both the Provider Product and the Feature. The FK constraints thus prevent inserting a Provider Feature for a Product Feature combination not already instantiated as a Product Feature possibility.

This solution assumes that a feature is identified by a Product - that is a feature cannot exist outside the context of a product. That assumption is likely valid as features, in and of them selves, have no meaning with the context of a product. However, if you do want to instantiate features that can included on multiple products, then you simply add a Feature table and make the current Feature table a Product_Feature table.

Often database designers assume each table must have its own "identifier." Nothing could be further from the truth. Please see Fabian Pascal's excellent blog post on this topic. Adding a surrogate Key to each table means the natural business rule you are trying to enforce can no longer be enforced declaratively via PK and FK constraints, just as you have found. If you must have a SK for some reason your only option is to enforce the constraint procedurally using a trigger. This option is much more problematic. First, you have to write, debug, test and maintain the trigger. The book Applied Mathematics for Database Professionals provides excellent detail on how difficult it can turn out to be to express constraints using procedural logic in a way that performs adequately. Secondly, triggers in and of them selves can sometimes be performance inhibitors as they execute serially.

My recommendation is to use the natural declarative approach.