Enforce constraint after design change

PRODUCT:

     PRODUCT_ID PRODUCT_NAME
     ========== ============
            100 Foo
            200 Bar

We have product types:

TYPE:

     TYPE_ID TYPE_NAME
     ======= =========
           1 Regular
           2 Extra

Products belong to types:

PRODUCT_TO_TYPE:

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

     PRODUCT_ID TYPE_ID
     ========== =======
            100       1
            200       2
            100       2 <-- violated constrain

At this point, there was a feature request. We have departments:

DEPARTMENT:

     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:

     TYPE_ID TYPE_NAME DEPT_ID
     ======= ========= =======
           1 Regular        10
           2 Extra          10
           3 Regular        20
           4 Plus           20

And now the new relationships:

PRODUCT_TO_TYPE:

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