RE: Data modeling question

From: Thomas Jeff <jeff.thomas_at_thomson.net>
Date: Wed, 11 May 2005 08:12:43 -0500
Message-ID: <428BC948616D534DA0860EDF33F542AB17A58A@INDYSMAIL02.am.thmulti.com>

Tom,

Thanks for the reply, but as I see it, this would allow the same user to be an authority multiple=20
times for any given db_name/schema_name. =20

Jeff

-----Original Message-----
From: Mercadante, Thomas F (LABOR)
[mailto:Thomas.Mercadante_at_labor.state.ny.us]=20
Sent: Wednesday, May 11, 2005 7:56 AM
To: Thomas Jeff; oracle-l_at_freelists.org
Subject: RE: Data modeling question

Jeff,

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.

Hope this helps.

Tom

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Thomas Jeff
Sent: Wednesday, May 11, 2005 8:38 AM
To: oracle-l_at_freelists.org
Subject: Data modeling question

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

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=3D20
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=3D20
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,=3D20 using before triggers to implement the business rules, and after triggers to maintain the=3D20 shadow = table.=3D20

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.

Email: jeff.thomas_at_thomson.net

Indy DBA Master Documentation available at: http://gkmqp.tce.com/tis_dba

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

Received on Wed May 11 2005 - 09:17:15 CDT