We create a output document (based on a template) to send to a customer for each product they buy. Templates differ per product (many products to one template), but currently all customers share the same templates
1) PRODUCTS
----------------
PRODUCT_ID (PK),
TEMPLATE_ID (FK)
2) TEMPLATES
------------
TEMPLATE_ID
3) CUSTOMERS
------------
CUSTOMER_ID
Now there is a requirement to have customized customer templates for one or two products. I'm not sure about the best way to design this. Some options:
A) New table (intersection) to define new many-to-many (many products to many templates)
PRODUCT_TEMPLATES
-------------------------------------------
PRODUCT_ID CUSTOMER_ID TEMPLATE_ID
1000 650 10
1000 760 10
1100 650 10
1100 760 10
1000 20
2000 30
3000 40
4000 40
5000 40
6000 60
Disadvantages: will mostly have null columns for customer_id, as assuming that's the default "ALL CUSTOMERS" - so can't define primary key on all 3 columns. we have too many customers in the database to include all in this table. (we'll only have exceptions for a handlful out of thousands of customers)
B) Override table (same as above, but only include the exceptions)
CUSTOMER_PRODUCT_TEMPLATES
---------------------------
PRODUCT_ID CUSTOMER_ID TEMPLATE_ID
1000 650 10
1000 760 10
Disadvantages: although can define the primary key on the 3 columns, I don't like this override solution - the data model still defines a (product) many to (template) one relationship, and this kind of sneakily breaks that relationship elsewhere.
To be honest I'm not happy about either option. How should these types of special cases be normally handled?