Re: REFERENCES constraint
От | Josh Berkus |
---|---|
Тема | Re: REFERENCES constraint |
Дата | |
Msg-id | web-100993@davinci.ethosmedia.com обсуждение исходный текст |
Ответ на | Re: REFERENCES constraint (Cedar Cox <cedarc@visionforisrael.com>) |
Список | pgsql-sql |
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
Вложения
В списке pgsql-sql по дате отправления: