views:

56

answers:

0

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?