Data modeling question

  • From: "Thomas Jeff" <jeff.thomas@xxxxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 11 May 2005 07:37:43 -0500

I was wondering if there was an elegant way to model (or implement the
business rule)=20
for those situations where the requirement in a parent-child
relationship is such=20
that there a quantity restriction on the child table.  =20

Consider the following two tables:

Table A            Table B
DB_NAME            DB_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
authorities for the given database/schema.   A given user can be either
or both a change=20
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=20
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,=20
using before triggers to implement the business rules, and after
triggers to maintain the=20
shadow table.=20

I'm sure this problem has cropped up before and would appreciate knowing
how you implemented
such a requirement.


Jeffery D Thomas
Thomson Information Services
Thomson, Inc.

Email: jeff.thomas@xxxxxxxxxxx

Indy DBA Master Documentation available at:


Other related posts: