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