| 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
Timely comment about the model breaking down with respect to a variable
maximum number of enrollees,=20
because the potential for such a requirement was just brought to our
attention -- now instead of
up to a max of 3 per authorization type, they are wanting to add SQL
Server and DB2 to the mix, with
SQL Server having a max of 5 for example. =20
-----Original Message-----
From: MacGregor, Ian A. [mailto:ian_at_slac.stanford.edu]=20
Sent: Thursday, May 12, 2005 12:28 PM
To: MacGregor, Ian A.; Thomas Jeff; oracle-l_at_freelists.org
Subject: 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 -------------------------------------------------------------------------=3D
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:53:10 CDT
 |
 |