Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Data modeling question

RE: Data modeling question

From: Mercadante, Thomas F (LABOR) <Thomas.Mercadante_at_labor.state.ny.us>
Date: Thu, 12 May 2005 14:52:19 -0400
Message-ID: <ABB9D76E187C5146AB5683F5A07336FF16E5D6@EXCNYSM0A1AJ.nysemail.nyenet>


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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Thomas Jeff Sent: Thursday, May 12, 2005 1:46 PM
To: MacGregor, Ian A.; oracle-l_at_freelists.org 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_at_slac.stanford.edu]=3D20 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.=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_at_slac.stanford.edu=3D20

-----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
=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_at_slac.stanford.edu
=3D3D20

-----Original Message-----
From: oracle-l-bounce_at_freelists.org =3D3D [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)=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_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-l
--
http://www.freelists.org/webpage/oracle-l
Received on Thu May 12 2005 - 15:01:09 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US