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.
