| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Data modeling question
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)
/
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_at_slac.stanford.edu
=20
-----Original Message-----
From: oracle-l-bounce_at_freelists.org =
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Thomas Jeff
Sent: Wednesday, May 11, 2005 5:38 AM
To: oracle-l_at_freelists.org
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.
Email: jeff.thomas_at_thomson.net
Indy DBA Master Documentation available at: http://gkmqp.tce.com/tis_dba
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Thu May 12 2005 - 11:16:08 CDT
 |
 |