| 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
I should have added that I much prefer Jared's method. It is a much =
more relational approach. New types of authorizations can be added =
without touching any code. If any change in data causes you to change =
your code, then you have a bad model. Adding another authorization type =
wouldd result in recreating the materialized view. Not a good thing.=20
Rules can also change. For instance one could change the rules so that = each authorization could hanve four people assigned instead of three. = In both cases constraints would need to be redone. A trivial matter
If for some reason one wanted to have some authorization types having a = maximum of three enrollees and others with four, then Jared's model = breaks down. It is much easier to simply change the constraints on the = materialized view. Unlike with data, it is not true that if your model = breaks down due to a rule change that you had a bad model. The model = was cast to enforce the rules as they were at that time.
The only thing I would would add to Jared's method is some sort of = sequence within parent code for the authorizations table so that one = didn't have to guess whether it was the first, second or third = authorization.=20
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 MacGregor, Ian A.
Sent: Thursday, May 12, 2005 8:10 AM
To: jeff.thomas_at_thomson.net; oracle-l_at_freelists.org
Subject: RE: Data modeling question
One way to enforce counts is via a materialied view. I'll start with =
=3D
table b which of course should have an foreign key referencing tabvle a, =
=3D
but I'm too lazy to build a table A. =3D20
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 =
=3D
null,
usr_indicator varchar2(1) default 'N' NOT NULL)
/
alter table b
add constraint b_pk=3D20
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,=3D20
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=3D20
group by db_name, schema_name
/
Now constrain the view
alter table b_limit
add constraint count_auth_indicator_ck
check(count_auth_indicator <=3D3D3)
/
alter table b_limit
add constraint count_usr_indicator_ck
check(count_usr_indicator <=3D3D3)
/
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 -------------------------------------------------------------------------=
Ian MacGregor
Stanford Linear Accelerator Center
ian_at_slac.stanford.edu
=3D20
-----Original Message-----
From: oracle-l-bounce_at_freelists.org =3D
[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)=3D3D20
for those situations where the requirement in a parent-child
relationship is such=3D3D20
that there a quantity restriction on the child table. =3D3D20
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=3D3D20
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=3D3D20
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,=3D3D20
using before triggers to implement the business rules, and after
triggers to maintain the=3D3D20
shadow table.=3D3D20
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-l -- http://www.freelists.org/webpage/oracle-lReceived on Thu May 12 2005 - 13:33:52 CDT
 |
 |