Enforce constraint after design change
Date: Fri, 17 Jul 2009 15:00:37 +0200
We have products:
PRODUCT_ID PRODUCT_NAME ========== ============ 100 Foo 200 Bar
We have product types:
TYPE_ID TYPE_NAME ======= ========= 1 Regular 2 Extra
Products belong to types:
PRODUCT_ID TYPE_ID ========== ======= 100 1 200 2
A product belongs to at most one type: there's a unique index on PRODUCT_ID so you can't do this:
PRODUCT_ID TYPE_ID ========== ======= 100 1 200 2 100 2 <-- violated constrain
At this point, there was a feature request. We have departments:
DEPT_ID DEPT_NAME ======= ============ 10 ACCOUNTING 20 SALES
and they want that product type is a per-department setting. So my first idea was to add a new column to the TYPE table:
TYPE_ID TYPE_NAME DEPT_ID ======= ========= ======= 1 Regular 10 2 Extra 10 3 Regular 20 4 Plus 20
And now the new relationships:
PRODUCT_ID TYPE_ID ========== ======= 100 1 200 2 100 4 200 3
But, of course, I have to remove the unique index on PRODUCT_ID. The problem is: how do I enforce my new condition? Now a product belongs to at most one type *and* department combination; PRODUCT_TO_TYPE contains no infor about departments.
I see no reasonable way to do it with the current design (a BEFORE INSERT/UPDATE trigger that runs a SQL query does not seem reasonable, does it?).
Can you think of a simple design change that could help?
Thank you in advance.
- http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
- Mi sitio sobre programación web: http://borrame.com
- Mi web de humor satinado: http://www.demogracia.com --