Re: Data modeling question

From: Jared Still <jkstill_at_gmail.com>
Date: Wed, 11 May 2005 19:00:43 +0100
Message-ID: <bf463805051111006e2044a5@mail.gmail.com>

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_at_earthlink.net> 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.

> 

>


--
http://www.freelists.org/webpage/oracle-l

Received on Wed May 11 2005 - 14:05:15 CDT