RE: Data modeling question
- From: "MacGregor, Ian A." <ian@xxxxxxxxxxxxxxxxx>
- To: <jeff.thomas@xxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 12 May 2005 08:10:25 -0700
One way to enforce counts is via a materialied view. I'll start with =
table b which of course should have an foreign key referencing tabvle a, =
but I'm too lazy to build a table A. =20
Create table b
(db_name varchar2(10) not null, schema_name varchar2(10) not null,
userid number(5,0) not null, auth_indicator varchar2(1) default 'N' not =
null,
usr_indicator varchar2(1) default 'N' NOT NULL)
/
alter table b
add constraint b_pk=20
primary key(db_name, schema_name, userid)
/
alter table b add constraint
usr_indicator_ck
check(usr_indicator in ('N', 'Y'))
/
alter table b add constraint
auth_indicator_ck
check(auth_indicator in ('N', 'Y'))
Next create the materialized view:
create materialized view b_limit
refresh on commit
as
select db_name, schema_name,=20
sum(decode(auth_indicator,'Y', 1, 'N', 0, 0)) as count_auth_indicator,
sum(decode(usr_indicator,'Y', 1, 'N', 0, 0)) as count_usr_indicator
from b=20
group by db_name, schema_name
/
Now constrain the view
alter table b_limit
add constraint count_auth_indicator_ck
check(count_auth_indicator <=3D3)
/
alter table b_limit
add constraint count_usr_indicator_ck
check(count_usr_indicator <=3D3)
/
---------------------------------------------------------------
Test it
SQL> insert into b values ('PROD', 'SYSTEM', 1, 'Y', 'N');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from b_limit;
DB_NAME SCHEMA_NAM COUNT_AUTH_INDICATOR COUNT_USR_INDICATOR
---------- ---------- -------------------- -------------------
PROD SYSTEM 1 0
SQL> insert into b values ('PROD', 'SYSTEM', 2, 'N', 'Y');
commit;
select * from b_limit;
1 row created.
SQL>
Commit complete.
SQL> select * from b_limit;
DB_NAME SCHEMA_NAM COUNT_AUTH_INDICATOR COUNT_USR_INDICATOR
---------- ---------- -------------------- -------------------
PROD SYSTEM 1 1
SQL> insert into b values ('PROD', 'SYSTEM', 3, 'Y', 'Y');
commit;
select * from b_limit;
1 row created.
SQL>
Commit complete.
SQL>
DB_NAME SCHEMA_NAM COUNT_AUTH_INDICATOR COUNT_USR_INDICATOR
---------- ---------- -------------------- -------------------
PROD SYSTEM 2 2
SQL> insert into b values ('PROD', 'SYSTEM', 4, 'Y', 'N');
commit;
select * from b_limit;
1 row created.
SQL>
Commit complete.
SQL> select * from b_limit;
DB_NAME SCHEMA_NAM COUNT_AUTH_INDICATOR COUNT_USR_INDICATOR
---------- ---------- -------------------- -------------------
PROD SYSTEM 3 2
SQL> insert into b values ('PROD', 'SYSTEM', 5, 'N', 'Y');
commit;
select * from b_limit;
1 row created.
SQL>
Commit complete.
SQL>
DB_NAME SCHEMA_NAM COUNT_AUTH_INDICATOR COUNT_USR_INDICATOR
---------- ---------- -------------------- -------------------
PROD SYSTEM 3 3
SQL> insert into b values ('PROD', 'SYSTEM', 6, 'Y', 'Y');
commit;
select * from b_limit;
1 row created.
SQL> commit
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-02290: check constraint (ORACLE.COUNT_USR_INDICATOR_CK) violated
SQL>
DB_NAME SCHEMA_NAM COUNT_AUTH_INDICATOR COUNT_USR_INDICATOR
---------- ---------- -------------------- -------------------
PROD SYSTEM 3 3
SQL> select * from b;
DB_NAME SCHEMA_NAM USERID A U
---------- ---------- ---------- - -
PROD SYSTEM 2 N Y
PROD SYSTEM 3 Y Y
PROD SYSTEM 1 Y N
PROD SYSTEM 4 Y N
PROD SYSTEM 5 N Y
SQL> insert into b values ('DEV', 'SYSTEM', 1, 'Y', 'N');
commit;
select * from b_limit;
1 row created.
SQL>
Commit complete.
SQL>
DB_NAME SCHEMA_NAM COUNT_AUTH_INDICATOR COUNT_USR_INDICATOR
---------- ---------- -------------------- -------------------
DEV SYSTEM 1 0
PROD SYSTEM 3 3
SQL> select * from b;
DB_NAME SCHEMA_NAM USERID A U
---------- ---------- ---------- - -
PROD SYSTEM 2 N Y
PROD SYSTEM 3 Y Y
PROD SYSTEM 1 Y N
PROD SYSTEM 4 Y N
PROD SYSTEM 5 N Y
DEV SYSTEM 1 Y N
-------------------------------------------------------------------------=
----------------------------------------
Some of the messages are out of order as I was cutting and pasting the =
commands as a block instead of issuing them individually and awaiting a =
response.
Ian MacGregor
Stanford Linear Accelerator Center
ian@xxxxxxxxxxxxxxxxx
=20
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx =
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Thomas Jeff
Sent: Wednesday, May 11, 2005 5:38 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Data modeling question
I was wondering if there was an elegant way to model (or implement the
business rule)=3D20
for those situations where the requirement in a parent-child
relationship is such=3D20
that there a quantity restriction on the child table. =3D20
Consider the following two tables:
Table A Table B
DB_NAME DB_NAME
SCHEMA_NAME SCHEMA_NAME
USR_ID -- user (authority)
AUTH_INDICATOR -- change authority
USR_INDICATOR -- user authority
In a nutshell, the rule is that there can be no more than 3 change or
user
authorities for the given database/schema. A given user can be either
or both a change=3D20
and user authority for a specific database/schema, and can be an
authority for multiple
database/schemas. So, given the model/business rule, there could be
anywhere from 3=3D20
to 6 child records for a given database/schema.
When first presented with this model, my initial thought was to add a
shadow table to Table B,=3D20
using before triggers to implement the business rules, and after
triggers to maintain the=3D20
shadow table.=3D20
I'm sure this problem has cropped up before and would appreciate knowing
how you implemented
such a requirement.
Thanks.
--------------------------------------------
Jeffery D Thomas
DBA
Thomson Information Services
Thomson, Inc.
Email: jeff.thomas@xxxxxxxxxxx
Indy DBA Master Documentation available at:
http://gkmqp.tce.com/tis_dba
--------------------------------------------
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
