Re: Enforce constraint after design change

From: Álvaro G. Vicario <alvaro.NOSPAMTHANX_at_demogracia.com.invalid>
Date: Mon, 20 Jul 2009 14:00:22 +0200
Message-ID: <h41m8r$87v$1_at_news.eternal-september.org>

Vladimir M. Zakharychev escribió:

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

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

However, if a TYPE exists it needs to have a DEPT_ID. It can't depend on having a PRODUCT attached to it.

> Your initial one-to-one constraint on (PRODUCT_ID,TYPE_ID) still > stands

Right, that's the primary key on PRODUCT_TO_TYPE.

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

I think I got the general idea. I probably need to move TYPE.DEPT_ID to a new table:

TYPE_TO_DEPT

	TYPE_ID DEPT_ID
	======= =======
               1      10
               2      10
               3      20
               4      20

Then I can link TYPE and PRODUCT_TO_TYPE through it. However than breaks another constraint I omitted in my original message for the sake of simplicity: TYPE names must differ within departments (but can and will often be duplicated between departments).

Anyway, thank you for your pointer. I guess I'll build a full-fledged example and elaborate on it.

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

Received on Mon Jul 20 2009 - 07:00:22 CDT