RE: Data modeling question
- From: "Mercadante, Thomas F (LABOR)" <Thomas.Mercadante@xxxxxxxxxxxxxxxxx>
- To: <jeff.thomas@xxxxxxxxxxx>, "MacGregor, Ian A." <ian@xxxxxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 12 May 2005 14:52:19 -0400
Jeff,
You can control this with Jared's model. You can always add another
column to his "database" table indicating the maximum number of
authorization types. It would make it more flexible anyway.
Tom
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Thomas Jeff
Sent: Thursday, May 12, 2005 1:46 PM
To: MacGregor, Ian A.; oracle-l@xxxxxxxxxxxxx
Subject: RE: Data modeling question
Timely comment about the model breaking down with respect to a variable
maximum number of enrollees,=3D20
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. =3D20
-----Original Message-----
From: MacGregor, Ian A. [mailto:ian@xxxxxxxxxxxxxxxxx]=3D20
Sent: Thursday, May 12, 2005 12:28 PM
To: MacGregor, Ian A.; Thomas Jeff; oracle-l@xxxxxxxxxxxxx
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.=3D20
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.=3D20
Ian MacGregor
Stanford Linear Accelerator Center
ian@xxxxxxxxxxxxxxxxx=3D20
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of MacGregor, Ian A.
Sent: Thursday, May 12, 2005 8:10 AM
To: jeff.thomas@xxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: RE: Data modeling question
One way to enforce counts is via a materialied view. I'll start with =
=3D
=3D3D
table b which of course should have an foreign key referencing tabvle a,
=3D3D but I'm too lazy to build a table A. =3D3D20
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
=3D3D null, usr_indicator varchar2(1) default 'N' NOT NULL) / alter =
table
=3D
b
add constraint b_pk=3D3D20 primary key(db_name, schema_name, userid) / =
=3D
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,=3D3D20
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=3D3D20 group by db_name, schema_name /
Now constrain the view
alter table b_limit
add constraint count_auth_indicator_ck check(count_auth_indicator =3D
<=3D3D3D3)
/
alter table b_limit
add constraint count_usr_indicator_ck
check(count_usr_indicator <=3D3D3D3)
/
---------------------------------------------------------------
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
------------------------------------------------------------------------
-=3D3D
----------------------------------------
Some of the messages are out of order as I was cutting and pasting the =
=3D
=3D3D
commands as a block instead of issuing them individually and awaiting a
=3D3D response.
Ian MacGregor
Stanford Linear Accelerator Center
ian@xxxxxxxxxxxxxxxxx
=3D3D20
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx =3D3D
[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)=3D3D3D20 for those situations where the requirement in a
parent-child relationship is such=3D3D3D20 that there a quantity =3D
restriction
on the child table. =3D3D3D20
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=3D3D3D20
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=3D3D3D20
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,=3D3D3D20 using before triggers to implement the
business rules, and after triggers to maintain the=3D3D3D20 shadow
table.=3D3D3D20
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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
