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

On Jul 17, 5:00*pm, "Álvaro G. Vicario"
<alvaro.NOSPAMTH... (AT) demogracia (DOT) com.invalid> wrote:
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

On Jul 17, 8:00*am, "Álvaro G. Vicario"
<alvaro.NOSPAMTH... (AT) demogracia (DOT) com.invalid> wrote:
This may not be simple but it does work:

SQL> create table product(
2 product_id number not null,
3 product_name varchar2(30) not null
4 );

Table created.

SQL>
SQL> alter table product
2 add constraint product_pk
3 primary key(product_id);

Table altered.

SQL>
SQL> create table prod_type(
2 type_id number not null,
3 type_name varchar2(30) not null
4 );

Table created.

SQL>
SQL> alter table prod_type
2 add constraint prodtype_pk
3 primary key(type_id);

Table altered.

SQL>
SQL> create table department(
2 dept_id number not null,
3 dept_name varchar2(20) not null
4 );

Table created.

SQL>
SQL> alter table department
2 add constraint department_pk
3 primary key(dept_id);

Table altered.

SQL>
SQL> create table dept_type(
2 dept_type_cd number not null,
3 dept_id number not null,
4 type_id number not null
5 );

Table created.

SQL>
SQL> alter table dept_type
2 add constraint dept_type_pk
3 primary key(dept_type_cd);

Table altered.

SQL>
SQL> alter table dept_type
2 add constraint dept_type_uk
3 unique(dept_id, type_id);

Table altered.

SQL>
SQL> create table product_to_type(
2 prod_type_cd number not null,
3 product_id number not null,
4 dept_type_cd number not null
5 );

Table created.

SQL>
SQL> alter table product_to_type
2 add constraint proddepttype_pk
3 primary key(prod_type_cd);

Table altered.

SQL>
SQL> alter table product_to_type
2 add constraint prodid_fk
3 foreign key(product_id)
4 references product;

Table altered.

SQL>
SQL> alter table product_to_type
2 add constraint prodtype_fk
3 foreign key(dept_type_cd)
4 references dept_type;

Table altered.

SQL>
SQL> insert all
2 into product
3 values(100, 'Foo')
4 into product
5 values(200, 'Bar')
6 into prod_type
7 values(1, 'Regular')
8 into prod_type
9 values(2, 'Extra')
10 into department
11 values(10, 'ACCOUNTING')
12 into department
13 values(20, 'SALES')
14 into dept_type
15 values(1, 10, 1)
16 into dept_type
17 values(2, 10, 2)
18 into dept_type
19 values(3, 20, 1)
20 into dept_type
21 values(4, 20, 2)
22 into product_to_type
23 values(101, 100, 1)
24 into product_to_type
25 values(102, 100, 2)
26 into product_to_type
27 values(201, 200, 3)
28 into product_to_type
29 values(202, 200, 4)
30 select * from dual;

14 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> create materialized view log on product_to_type with rowid,
(product_id) including new values;

Materialized view log created.

SQL>
SQL> create materialized view prod_type_dept_ck
2 refresh fast on commit
3 as
4 select product_id, count(*) ct from product_to_type
5 group by product_id
6 /

Materialized view created.

SQL>
SQL> alter table prod_type_dept_ck
2 add constraint check_ct
3 check(ct < 3)
4 /

Table altered.

SQL>
SQL> select *
2 from product_to_type;

PROD_TYPE_CD PRODUCT_ID
DEPT_TYPE_CD
------------ ----------
------------
101 100
1
102 100
2
201 200
3
202 200
4

SQL>
SQL> insert
2 into product_to_type
3 values(203, 200, 2);

1 row created.

SQL>
SQL> commit;
commit
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-02290: check constraint (BING.CHECK_CT) violated


SQL>
SQL> select *
2 from product_to_type;

PROD_TYPE_CD PRODUCT_ID
DEPT_TYPE_CD
------------ ----------
------------
101 100
1
102 100
2
201 200
3
202 200
4

SQL>


David Fitzjarrell

Vladimir M. Zakharychev escribió:

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

Right, that's the primary key on PRODUCT_TO_TYPE.

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

ddf escribió:

I still have to understand your code to its full extent (thanks!) but I
figure out that the key is the materialized view (a feature I haven't
studied yet).

Can constraints on materialized views prevent the modification of the
source tables? Is that the whole point?

And, does the materialized view log play any role on this or it's there
just to provide an acceptable performance?



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

On Jul 20, 7:30*am, "Álvaro G. Vicario"
<alvaro.NOSPAMTH... (AT) demogracia (DOT) com.invalid> wrote:
You cannot implement a FAST refresh without a materialized view log.
Notice the materialized view uses a fast refresh on commit; when
transactions on the source table are committed then the refresh
occurs, and in this case the refresh violates the check constraint
which invalidates the commit on the source table.


David Fitzjarrell

ddf escribió:

It was difficult to grasp the correct syntax. I eventually got this:


CREATE MATERIALIZED VIEW LOG ON TYPE
WITH PRIMARY KEY, ROWID
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON PRODUCT_TO_TYPE
WITH PRIMARY KEY, ROWID
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW PRODUCT_TYPE_DEPT_CHK
REFRESH FAST ON COMMIT
AS
SELECT A.PRODUCT_ID, B.DEPT_ID,
A.ROWID AS ROWID_AT, B.ROWID AS ROWID_TA
FROM PRODUCT_TO_TYPE A, TYPE B
WHERE A.TYPE_ID=B.TYPE_ID
/

ALTER TABLE PRODUCT_TYPE_DEPT_CHK
ADD CONSTRAINT PRODUCT_TYPE_DEPT_CHK_PK PRIMARY KEY(
PRODUCT_ID,
DEPT_ID
);


I'll do further testing but it seems to work. I suppose that performance
should not be too bad if TYPE and PRODUCT_TO_TYPE do not change often.



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