How to avoid problems with composite entity keys and constraints (Products, Options, Option Groups, Special Orders)

Question

I am modeling a database for a webshop and have come across ad issue. Basically the question is whether to ignore database normalization rules for simplicity's sake. Below is the relevant part of my diagram prior to the issue. Database diagram Basically, the product can have options (size, flavor, color) but only from one option group. Since an option group can have many options and a product that uses it can take a subset, a ProductOption table is created. Next we have a SpecialOffers table. Next, a special offer can have many products and products can belong to many special offers, hence the association table SpecialOfferProducts. All this works fine until the special offer includes a product that has options. This is where I run into problems. I have a couple of ideas.

First idea: Create an association table between SpecialOfferProducts and ProductOptions. I don't like this idea since both tables have composite primary keys and creating a table that has a composite primary key composed of two composite primary keys seems really weird and I have never seen anything like it.

Second idea: Create a association table between SpecialOfferProducts and Options. This seems wrong since Options is not directly tied to Product. Still this would work and the primary key would be a little simpler.

Third idea: This is the one that I like the most but it violates a few rules. Change the SpecialOfferProducts table. Make it have its own primary key and have SpecialOffers, Products and Options as foreign keys. Simply make the Options foreign key nullable and problem solved. Of course the problems are that I am not making an association table where I should and am making a foreign key nullable. This would slightly complicate my code to deal with all of this but I still feel that this is much simpler than the other approaches since I reduce the number of composite keys and I don't have to add another table in the case where the product in a special offer uses an option.

My question is, which one of this options is best? Is there a better option I have not mentioned?

Using Martin style notation

OptionGroups has (0,n) relationship with the table Options. Options has (1,1) relationship with the table OptionGroups. The purpose of these table is to store information like color, size, etc. An example wouldbe OptionGroups entry color that has Option entries black, white, etc.

Product table has (0,1) relationship with table OptionGroups. OptionGroups has (0,n) relationship with table Product. Product table has a (o,n) relationship with the table Options. Options table has a (o,n) relationship with the table Product. Many-to-many relation produces association table ProductOptions. ProductOptions has a composite PK ProductID, OptionsID. The purpose of these tables is to allow product to have (but does not have to have) options from a certain option group but does not need to have all options from that group.

Example 1. Product does not have any options, hence FK Product_OptionGroups is null. In this case the product does not have any entries in the ProductOptions table.

Example 2. Product has options (lets say color) and so the FK Product_OptionGroups is not null (has the ID of the coresponding option group). Option group color can have many colors and the product is allowed to use one or many of those colors. The colors in use by the product are entries in the table ProductOptions.

SpecialOffer table has a (1,n) relation to the table Products. Products table has a (0,n) relation to the table SpecialOffer. Many-to-many relation creates the association table SpecialOfferProducts. This table has a PK SpecialOfferID, ProductID. The table has a Quantity attribute indicating the quantity of the product.

Example. SpecialOffer A includes one instance of Product A and two instances Product B.

Lets say that the Product A has options. Now SpecialOfferProducts table must reference the correct option.(maybe the product can be blue and red and the special offer only includes the red product). This is where the current schema does not work and either an additional table must be introduced (idea 1 and 2) or the existing tables changed (idea 3).

Answer 1

Maybe you have some relation(ship)/association not representable in terms of your first three:

-- special offer S offers the pairing of P and option O
SpecialOfferProductOption(S, P, O)
-- PK (S, P, O)
-- FK (S, P) to SpecialOfferProducts, FK (P, O) to ProductOptions

You don't seem to understand the use of composite keys, CKs (candidate key), FKs (foreign keys) & constraints. Constraints (PKs, UNIQUE, FKs, etc) arise after you design relation(ship)s/associations sufficient to clearly describe your business situations (represented by tables), per the situations that can arise.

From an ER point of view, you are not properly applying the notions of participating entity (type), entity (type) key & associative entity (type).

You are needlessly & vaguely afraid of composite CKs. Even if you wanted to reduce use of composite keys, you should first find a straightforward design. If you don't want to use composite keys, introduce id PKs along with other CKs. But note that when you use ids as FKs that doesn't drop the obligation to properly constrain the tables that they appear in to agree where necessary with other ids or columns per the constraints you would have needed if you had used the composite CKs instead.

First idea: Create an association table between SpecialOfferProducts and ProductOptions. I don't like this idea since both tables have composite primary keys and creating a table that has a composite primary key composed of two composite primary keys seems really weird and I have never seen anything like it.

It's not clear what you mean by this. Maybe you mean the above (good) design. Maybe you mean having duplicate product columns; but that's not what good design suggests.

From an ER perspective: You may be thinking of this as a relation(ship)/association on special orders & products. But then the entity keys would not be composite, they would identify special orders & products, and also options would participate. Or we can use the ER concept of reifying relation(ship)s/associations SpecialOfferProducts & ProductOffers to associative entities that are the two participants. That would use composite keys. (If options weren't considered entities then ER would call this a weak relation(ship)/association entity with special orders & products as identifying entities.) Regardless, special orders & products must agree on options, and if that isn't enforced via FKs then it still needs constraining.

If you have (been) read(ing) some published text(s) on information modeling & database design (as you should) you will see many uses of composite keys.

Second idea: Create an association table between SpecialOfferProducts and Options. This seems wrong since Options is not directly tied to Product. Still this would work and the primary key would be a little simpler.

It's not clear what you mean by "directly tied", "seems" or "wrong".

Relational tables relation(ship)s/associations are among values, certain subrows of which may identify certain entities. Just use the relevant columns & declare the relevant constraints.

From an ER perspective: Considering that you seem to be confused about participant entities (special offer vs SpecialOfferProduct), maybe this is moot, but: Maybe if you tried to express yourself only using technical terms & without the confusion then you would be trying to say that this design needs a constraint that product-option pairs appear in ProductOptions and that it's messy that the constraint involves a relation(ship)/association whose associative entity ProductOption isn't one of the participating entities. I'd agree, but such a design is not "wrong".

Third idea: This is the one that I like the most but it violates a few rules. Change the SpecialOfferProducts table. Make it have its own primary key and have SpecialOffers, Products and Options as foreign keys. Simply make the Options foreign key nullable and problem solved.

Besides just being needlessly complex, this design is bad. It involves a complex table meaning & complex constraints. When settting the table value you need to decide when to use & not use nulls. When reading you need to figure out what a row means based on whether it has a null. Introducing an id or nulls, possibly while dropping columns, does not remove the obligation to constrain remaining columns if that's not handled by remaining FK constraints. Normally we combine tables while introducing nulls in columns that are not part of every CK--not your case. Here your adding ids doesn't even obviate the need to constrain pairs of products and non-null option column values to be in ProductOptions. And when there is a NULL option column value there should still exist certain rows in ProductOptions and sometimes not certain rows in SpecialOfferProducts. Also this design must be used with complex queries dealing with the presence of NULL. (Which you address.) Justifying this as an ER design is similarly problematic.

PS 1 Please explain your business relation(ship)s/associations with less generic terms than the essentially meaningless "has", "with", "uses", "in" & "belong to"--as you would with a client buying your products & special offers. They refer to relation(ship)s/associations & sets, but they don't explain them. (Similarly, cardinalities are properties of relation(ship)s/associations, but don't explain/characterize them.)

PS 2 ER reasoning about designs involves what (possibly associative) entities are participating in relationships, whereas in the relational model view tables just capture n-ary relation(ship)s/associations for any n. So the ER view is adding needless distinctions. That is why ER-based information modeling & database design approaches are not as effective as fact-based approaches:

This leads to inadequate normalization and constraints, hence redundancy and loss of integrity. Or when those steps are adequately done it leads to the E-R diagram not actually describing the application, which is actually described by the relational database predicates, tables and constraints. Then the E-R diagram is both vague, redundant and wrong.

PS 3 We don't need SpecialOfferProducts if it holds rows where "special offer S offers the pairing of P and some option", because it is select S, P from SpecialOfferProductOption. (This seems to be the case since your option 3 involves having only one table that you call SpecialOfferProducts but is like this table with an added id.) But if it holds rows where say "special offer S offers product P" and that can be so when not all of S's product-option pairs have been recorded then you need it. (Something similar arises re deciding when something is an entity, eg when there should be a table "S is a special option".)

PS 4

seems really weird and I have never seen anything like it

This is the story of life. But in a technical context if we learn and apply clearly defined basic definitions, rules & procedures then we "see" more, and more clearly. (And don't vaguely think we vaguely see things that aren't there.) And "weird" is a rare case where we can explicitly justify that our tools don't apply.