RE: Data modeling question

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.

The package holds an array (pl/sql table) composed of the table b keys for the table b records that are inserted or updated.

A pre-statement on insert and update trigger, to empty out the array.

A post-row on insert and update trigger, to add the record key to the array. (Updates only need to add the record key if the value of either of the two flags was changed. Inserts only need to add it if either of the flags is "Y".

A post-statement trigger on insert and update, that loops thru the array and checks the count of records for each of the two flags. It should empty the array and raise an application error if the rule is violated. If not, it just empties the array.

The array could be made to just hold the foreign key values to table a instead of the keys to table b. Whether that is done or not depends upon whether it would be faster to check for duplicates in the array than it would be to loop thru each row at the end, and upon whether there are other post-statement rules on table b to deal with. This optimization technique works really well with single column numeric primery keys, as the key value can be the array index value also.

Enjoy!

-----Original Message-----

Have you thought of something as simple as an additional column with a check constraint of values 1 thru 6? And then make this a part of the primary key of the child table:

Table B
PK DB_NAME
PK SCHEMA_NAME
PK RECORD_NUMBER <=3D=3D=3D values of 1 thru 6 only.

   USR_ID          -- user (authority)
   AUTH_INDICATOR  -- change authority
   USR_INDICATOR   -- user authority

Simple but effective.

--

http://www.freelists.org/webpage/oracle-l Received on Wed May 11 2005 - 09:30:49 CDT