Received: from davinci.ethosmedia.com (davinci.ethosmedia.com [209.10.40.250]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f7D26SP16297 for ; Sun, 12 Aug 2001 22:06:28 -0400 (EDT) (envelope-from josh@agliodbs.com) Received: from [209.10.40.250] (account ) by davinci.ethosmedia.com (CommuniGate Pro WebUser 3.4.1) with HTTP id 100993; Sun, 12 Aug 2001 19:06:20 -0700 From: "Josh Berkus" Subject: Re: REFERENCES constraint To: Cedar Cox Cc: pgsql-sql@postgresql.org X-Mailer: CommuniGate Pro Web Mailer v.3.4.1 Date: Sun, 12 Aug 2001 19:06:20 -0700 Message-ID: In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="_===100993====davinci.ethosmedia.com===_" X-Archive-Number: 200108/156 X-Sequence-Number: 4377 This is a multi-part MIME message --_===100993====davinci.ethosmedia.com===_ Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: 8bit Cedar, > I guess I wasn't clear. Let me try to explain again: > > CREATE TABLE obj_weights ( > object_id int4 REFERENCES ( apple_objects(id) OR > banana_objects(id) ) > weight float4, > ) > > "apple_objects" doesn't necessarily have anything to do with > "banana_objects". Ok, don't ask why you would want to store weights > of > apples and bananas in the same table.. (and if you know, please tell > me). > This is all actually for someone else's database that I just picked > up. > They did something like this.. single sequence for the whole > database, > multiple object tables, and a table(s) referencing objects that could > come > from any of those tables. Maybe this is just bad design. Thoughts > anyone? That actually makes a *lot* more sense. In fact, I'm doing the same thing with two tables in my database ... one called "mod_data" and another one called "notes". The first stores modifications users and timestamps for 6 different tables, the second stores scrolling user notes for foure different tables. Thus mod_data should look like: CREATE TABLE mod_data ( usq INT NOT NULL REFERENCES (clients(usq) OR candidates(usq) or orders(usq) or ... ) entry_user INT NOT NULL entry_date TIMESTAMP NOT NULL DEFAULT current_timestamp mod_user INT NOT NULL mod_date TIMESTAMP NOT NULL DEFAULT current_timestamp ) But, as you've observed, this is quite impossible. The entire structure of relationality has been constructed around the heirarchy and/or star topology; there is no provision for this kind of relationship. I can think of a number of good reasons not to attempt to implement REFERENCES for this kind of structure. So you can deal with it as I did: 1. Users have no ability to add or modify records in the child-of-many table. They must push data through functions that I have defined, which take care of creating/updating the dependant records. 2. All major tables subscribe to a single "Univeral Sequence" that supplies unique surrogate keys for the six tables. This makes the ID unique not only within but between the 6 tables. The one disadvantage is that it is a postgresql-only implementation. 3. Users cannot modify this "usq". Nor can they delete records. Thus my DB integrity is protected. In a database where user access is less restrained, you may find that the cost of creating all the triggers necessary to deal with user updates and deletes is a lot more than the effort to duplicate a few tables. An alternate approach is for you to define your own updatable views. While quite labor-intensive, this approach takes care of quite a few complex relationship structures by forcing the users to push their inserts and updates through the views. Properly designed, the user would not even realize that banana_weight and apple_weight are in the same table and are in a different table from banana_data and apple_data. Or, perhaps, as a very advanced user, I'm just making some very advanced mistakes ... -Josh Berkus ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete information technology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco --_===100993====davinci.ethosmedia.com===_ Content-Type: text/plain Content-Disposition: attachment; filename="" Content-Transfer-Encoding: base64 DQo= --_===100993====davinci.ethosmedia.com===_ Content-Type: text/plain Content-Disposition: attachment; filename="" Content-Transfer-Encoding: base64 DQo= --_===100993====davinci.ethosmedia.com===_ Content-Type: text/plain Content-Disposition: attachment; filename="" Content-Transfer-Encoding: base64 DQo= --_===100993====davinci.ethosmedia.com===_--