RE: Data modeling question
- From: "Thomas Jeff" <jeff.thomas@xxxxxxxxxxx>
- To: "Mercadante, Thomas F (LABOR)" <Thomas.Mercadante@xxxxxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 11 May 2005 08:12:43 -0500
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@xxxxxxxxxxxxxxxxx]=20
Sent: Wednesday, May 11, 2005 7:56 AM
To: Thomas Jeff; oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Thomas Jeff
Sent: Wednesday, May 11, 2005 8:38 AM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxx
Indy DBA Master Documentation available at: http://gkmqp.tce.com/tis_dba
--------------------------------------------
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
