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@(protected)]=20 Sent: Thursday, May 12, 2005 12:28 PM To: MacGregor, Ian A.; Thomas Jeff; oracle-l@(protected) 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@(protected)=20
-- --Original Message-- -- From: oracle-l-bounce@(protected) [mailto:oracle-l-bounce@(protected)] On Behalf Of MacGregor, Ian A. Sent: Thursday, May 12, 2005 8:10 AM To: jeff.thomas@(protected); oracle-l@(protected) 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) / -- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----- Test it
SQL> insert into b values ('PROD', 'SYSTEM', 1, 'Y', 'N');
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 -- ---- ---- ---- ---- ---- ---- ---- -- Some of the messages are out of order as I was cutting and pasting the = =3D commands as a block instead of issuing them individually and awaiting a =3D response.
Ian MacGregor Stanford Linear Accelerator Center ian@(protected) =3D20
-- --Original Message-- -- From: oracle-l-bounce@(protected) =3D [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)=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.
-- ---- ---- ---- ---- ---- ---- ---- ------ Jeffery D Thomas DBA Thomson Information Services Thomson, Inc.
