Data modeling question 2005-05-12 - By MacGregor, Ian A.
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 (See ORA-12008.ora-code.com): error in materialized view refresh path
ORA-02290 (See ORA-02290.ora-code.com): 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@(protected)
=20
-- --Original Message-- --
From: oracle-l-bounce@(protected) =
[mailto:oracle-l-bounce@(protected)] On Behalf Of Thomas Jeff
Sent: Wednesday, May 11, 2005 5:38 AM
To: oracle-l@(protected)
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@(protected)
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-l
|
|
