5

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          |                             
|                   |                             
+-------------------+                             
6
  • What is the purpose of PROVIDER_FEATURE? Can't you derive this information via the other tables, or are there additional attributes involved? Aug 16, 2015 at 15:38
  • Assuming you really need this table, in theory you can have a check constraint with a sub-select. Not many DBMS support this though. Another common way to enforce a rule like yours is to use triggers. Aug 16, 2015 at 16:09
  • @Lennart No therre are additional attributes involved. Aug 16, 2015 at 17:12
  • Then PROVIDER_FEATURE can be expressed as a view. Something like: CREATE VIEW PROVIDER_FEATURE AS select <product_key>, <feature_key> from PROVIDER_PRODUCT join PRODUCT on ... join FEATURE on .... Aug 16, 2015 at 17:19
  • Sorry, I said there ARE additional attributes involved. And not every product - feature combination is also a provider_product - provider_feature combination. Features can be on the generic product that are not on the provider_product Aug 16, 2015 at 17:41

1 Answer 1

6

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):

enter image description here

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.

3
  • Hi Todd. You said, "The Product Number in Provider_Feature is a FK back to both the Provider Product and the Feature." Are you saying there is a foreign key to the Product Number column in Provider_Feature from both Product Number columns in Provider_Product and Feature tables? Is this possible? Apr 10, 2016 at 19:48
  • Yes this is possible. The syntax is to declare 2 foreign key constraints in the product feature table. The first looks something like CONSTRAINT [name] FOREIGN KEY (Provider Number, Product Number) REFERENCES Provider_Product (Provider Number, Product Number). The second looks something like CONSTRAINT [name] FOREIGN KEY (Product Number, Feature Number) REFERENCES Feature (Product Number, Feature Number). The Oracle data modeler which I used to create the example will generate this DDL using what it calls "folded" keys. Apr 12, 2016 at 0:38
  • Thanks for clarifying. I guess my SQL is rusty because I forgot foreign key constraints can be used like that. I tested it out in a sql fiddle to be sure (the Oracle fiddle tool seems to be broken, but your design works in MySQL). If I got anything wrong in the fiddle, let me know and I'll correct it for posterity. Apr 16, 2016 at 3:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.