I think Tom had the right idea. The problem was the model provided.
I will use DDL, though it is not the same as modeling, it's easier to do in email. :)
Try this:
create table databases ( id number(12) not null, database_name varchar2(10) not null, primary key( id) ) /
create table schemas ( id number(12) not null, database_id number(12) not null references databases(id), schema_name varchar2(30) not null, primary key(id), constraint schema_db_unq unique(database_id, schema_name) ) /
create table users ( id number(12) not null, username varchar2(30) not null, primary key(id), constraint users_username_unq unique(username) ) /
create table auth_types ( id number(12) not null, auth_name varchar2(30) not null, primary key(id), constraint auth_types_ame_unq unique(auth_name) ) /
create table authorizations ( database_id number(12) not null references databases(id), schema_id number(12) not null references schemas(id), user_id number(12) not null references users(id), auth_type_id number(12) references auth_types(id), auth_limit number(2) check ( auth_limit between 1 and 3 ), primary key(database_id, schema_id, auth_type_id, auth_limit) ) /
-- create auth types insert into auth_types values(1,'User Authority'); insert into auth_types values(2,'Change Authority');
-- add databases insert into databases values(1,'PRD'); insert into databases values(2,'DEV');
-- add schemas insert into schemas values(1,1,'APP1'); insert into schemas values(2,1,'APP2'); insert into schemas values(3,2,'APP1'); insert into schemas values(4,2,'APP2');
-- add users insert into users values(1,'BARNEY'); insert into users values(2,'WILMA'); insert into users values(3,'FRED'); insert into users values(4,'BETTY');
-- create authorizations insert into authorizations(database_id, schema_id,user_id,auth_type_id,auth_limit) values(1,1,1,1,1); insert into authorizations(database_id, schema_id,user_id,auth_type_id,auth_limit) values(1,2,1,1,1); insert into authorizations(database_id, schema_id,user_id,auth_type_id,auth_limit) values(1,1,2,1,2); insert into authorizations(database_id, schema_id,user_id,auth_type_id,auth_limit) values(1,2,2,1,2); insert into authorizations(database_id, schema_id,user_id,auth_type_id,auth_limit) values(1,1,3,1,3); insert into authorizations(database_id, schema_id,user_id,auth_type_id,auth_limit) values(1,2,3,1,3);
insert into authorizations(database_id, schema_id,user_id,auth_type_id,auth_limit) values(1,1,1,2,1); insert into authorizations(database_id, schema_id,user_id,auth_type_id,auth_limit) values(1,2,1,2,1); insert into authorizations(database_id, schema_id,user_id,auth_type_id,auth_limit) values(1,1,2,2,2); insert into authorizations(database_id, schema_id,user_id,auth_type_id,auth_limit) values(1,2,2,2,2); insert into authorizations(database_id, schema_id,user_id,auth_type_id,auth_limit) values(1,1,3,2,3); insert into authorizations(database_id, schema_id,user_id,auth_type_id,auth_limit) values(1,2,3,2,3);
prompt oops, cannot add more authorizations to db 1 insert into authorizations(database_id, schema_id,user_id,auth_type_id,auth_limit) values(1,1,4,1,3); insert into authorizations(database_id, schema_id,user_id,auth_type_id,auth_limit) values(1,2,4,1,3); insert into authorizations(database_id, schema_id,user_id,auth_type_id,auth_limit) values(1,1,4,1,4); insert into authorizations(database_id, schema_id,user_id,auth_type_id,auth_limit) values(1,2,4,1,4);
commit;
select d.database_name ,s.schema_name ,u.username ,at.auth_name from databases d, schemas s, users u, auth_types at, authorizations a where a.database_id = d.id and a.schema_id = s.id and a.user_id = u.id and a.auth_type_id = at.id /
-- Jared Still Certifiable Oracle DBA and Part Time Perl Evangelist
On 5/11/05, david wendelken <davewendelken@(protected)> wrote: > > > Tom, > > That's an ingenious method, but it doesn't enforce the rule as stated (at > least as I understood it). > > With your method, it would be possible to have 6 user authorities and 0 > change authorities, which doesn't jive with what he was saying. (There can > be at most 3 of each type for the database schema.) > > Splitting Table B into two tables, B-change and B-user, plus your > technique with a check constraint with values from 1 to 3 would work. > > Jeff wrote to me and confirmed the reason he was considering using a > shadow table was to avoid a mutating table error caused by querying table b > whilst inserting or updating table b. If Jeff decides to go with a single > table and triggers to enforce the rule (instead of a shadow table and > triggers), here's one way to go about it. > > This technique uses 3 triggers and one package. > >
