Re: Enforce constraint after design change

From: Vladimir M. Zakharychev <vladimir.zakharychev_at_gmail.com>
Date: Fri, 17 Jul 2009 09:40:40 -0700 (PDT)
Message-ID: <c365a5eb-fe5a-422b-b5cd-ab6f5f575133_at_o15g2000yqm.googlegroups.com>

On Jul 17, 5:00 pm, "Álvaro G. Vicario"
<alvaro.NOSPAMTH..._at_demogracia.com.invalid> wrote:
> We have products:
>
> 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
> --

If I got your requirements correctly, you need to create one-to-one DEPT_TO_TYPE (DEPT_ID,TYPE_ID) relation with foreign key (TYPE_ID) on PRODUCT_TO_TYPE(TYPE_ID). That is, you can assign a product type to a department only if there is a product of such type and each type can be assigned to only one department. Your initial one-to-one constraint on (PRODUCT_ID,TYPE_ID) still stands, your new one-to-one constraint on (DEPT_ID, TYPE_ID) assigns each type to single department, and you have transitive relations between PRODUCT_ID and DEPT_ID and between DEPT_ID and TYPE via TYPE_ID.

Regards,

   Vladimir M. Zakharychev
   N-Networks, makers of Dynamic PSP(tm)    http://www.dynamicpsp.com Received on Fri Jul 17 2009 - 11:40:40 CDT