From oracle-l-bounce@freelists.org Wed May 11 09:00:23 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j4BE0Nta017963 for ; Wed, 11 May 2005 09:00:23 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id j4BE0H4Z017941 for ; Wed, 11 May 2005 09:00:21 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id D6DA21923D8; Wed, 11 May 2005 07:57:27 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 30289-07; Wed, 11 May 2005 07:57:27 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 6035E192499; Wed, 11 May 2005 07:57:27 -0500 (EST) X-MimeOLE: Produced By Microsoft Exchange V6.5.7226.0 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-type: text/plain; charset=us-ascii Content-Transfer-Encoding: 8bit Subject: RE: Data modeling question Date: Wed, 11 May 2005 08:55:39 -0400 Message-ID: X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Data modeling question Thread-Index: AcVWJjp56lWWgbwaSmWKHEPzYW6wzwAAhyDg From: "Mercadante, Thomas F (LABOR)" To: , X-OriginalArrivalTime: 11 May 2005 12:55:39.0893 (UTC) FILETIME=[BBC61A50:01C55628] X-archive-position: 19569 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: Thomas.Mercadante@labor.state.ny.us Precedence: normal Reply-To: Thomas.Mercadante@labor.state.ny.us X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.63 (2004-01-11) on air891.startdedicated.com X-Spam-Status: No, hits=0.1 required=5.0 tests=AWL autolearn=ham version=2.63 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@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of Thomas Jeff Sent: Wednesday, May 11, 2005 8:38 AM To: oracle-l@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@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