Обсуждение: Multitable uniqueness ?
Hi folks, Is there a way to have something like this : UNIQUE (table_1.id, table_2.xxx) I got some tables that have a couple of foreign keys. Now I try to minimize those relationships to clean up the mess. :-} We do business fairs. (???) Like c-bit only a few magnitudes smaller. So we have projects and rent stalls to customers. customers (c_id, ...) projects (p_id,...) there is an relation cust_proj (cp_id, c_fk, p_fk, status_fk) with a UNIQUE constraint (c_fk, p_fk) A customer can have several orders, contacts, ... tied to a project. Those look like this stalls (stall_id, cp_id, stall_no, ...) o_id PRIMARY cp_fk FOREIGN KEY that ties to custmer and project stall_no is a varchar It should be unique within a project. Will I have to integrate the project.id into the stalls-table ?
On Wed, May 26, 2004 at 05:13:14 +0200, Andreas <maps.on@gmx.net> wrote: > > Is there a way to have something like this : UNIQUE (table_1.id, > table_2.xxx) Postgres doesn't support database constraints at this time which is what you would need to do this simply. You can enforce this constraint by creating a third table that has the ids, id type and a separate id type field for each possible type that will be null except for the type field that matches the actual type. You also need to add a type field to the original two tables. Then you set up a composite foreign key from the new table to each of the per type tables using the id and the matching id type fields. Each per type table should have a foriegn key refernce for id to the combined table. This will enforce a 1-1 relationship between the combined table and the union of the per type tables. It is probably possible to get rid of the redundant copies of the field type using triggers, but I don't know that there is much of a benefit to doing so. The redundant values will all be kept in sync with constraints, so there isn't a data consistancy problem with doing it that way. This method is going to be more portable than using triggers. This method will probably be within a constant factor as efficient as anything you do with triggers.
Bruno Wolff III wrote: > On Wed, May 26, 2004 at 05:13:14 +0200, > Andreas <maps.on@gmx.net> wrote: >> >> Is there a way to have something like this : UNIQUE (table_1.id, >> table_2.xxx) > > Postgres doesn't support database constraints at this time which is > what you would need to do this simply. > > You can enforce this constraint by creating a third table that has the > ids, id type and a separate id type field for each possible type that > will be null except for the type field that matches the actual type. > You also need to add a type field to the original two tables. Then you > set up a composite foreign key from the new table to each of the per type > tables using the id and the matching id type fields. Each per type table > should have a foriegn key refernce for id to the combined table. > This will enforce a 1-1 relationship between the combined table and the > union of the per type tables. I think a third table with just the unique id plus custom triggers on both tables that insert/update/delete the id into/from the third table are totally enough. Jan > > It is probably possible to get rid of the redundant copies of the field type > using triggers, but I don't know that there is much of a benefit to doing so. > The redundant values will all be kept in sync with constraints, so there isn't > a data consistancy problem with doing it that way. This method is going to be > more portable than using triggers. This method will probably be within a > constant factor as efficient as anything you do with triggers. > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
Do you really need MANY-TO-MANY between customers and projects? I can see customers owning many projects, but do you really have projects belonging to many customers? If not, fold cust_proj into projects. Otherwise, UNIQUE (cp_id, stall_no) on stalls should be enough. Andreas wrote: > Hi folks, > > Is there a way to have something like this : UNIQUE (table_1.id, > table_2.xxx) > > I got some tables that have a couple of foreign keys. Now I try to > minimize those relationships to clean up the mess. :-} > We do business fairs. (???) Like c-bit only a few magnitudes smaller. > So we have projects and rent stalls to customers. > > customers (c_id, ...) > projects (p_id,...) > > there is an relation cust_proj (cp_id, c_fk, p_fk, status_fk) > with a UNIQUE constraint (c_fk, p_fk) > > A customer can have several orders, contacts, ... tied to a project. > > Those look like this stalls (stall_id, cp_id, stall_no, ...) > o_id PRIMARY > cp_fk FOREIGN KEY that ties to custmer and project > > stall_no is a varchar > It should be unique within a project. > > Will I have to integrate the project.id into the stalls-table ? > > > > ---------------------------(end of broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings >
Jean-Luc Lachance schrieb: > Do you really need MANY-TO-MANY between customers and projects? > I can see customers owning many projects, but do you really have > projects belonging to many customers? In this case yes. projects ( 1, 'x-fair 2003'; 2, 'y-fair 2003'; 3, 'x-fair 2004') customer ( 1, 'X ltd'; 2, 'Y'; 3, 'Z') maybe all 3 have a stall on project 1. c1 and c2 attend project 2 where c2 has 2 stalls one inside and one outside the hall. I have the cust_project relation to tie other objects like proposals and letters to something small and common for all the communication to a customer. > If not, fold cust_proj into projects. > Otherwise, UNIQUE (cp_id, stall_no) on stalls should be enough. cp_id implies uniquness of (customer, project) couples. So with UNIQUE (cp_id, stall_no) we get --> UNIQUE (c_id, p_id, stall_no) This'd be too much. Stall_no is the number of the cubicles the customers rent. It needs to be unique within on project so that we can relate on a specific spot on the area to send visitors when they ask us and we print those numbers in the flyer.
Bruno Wolff III wrote: >On Wed, May 26, 2004 at 05:13:14 +0200, > Andreas <maps.on@gmx.net> wrote: > > >>Is there a way to have something like this : UNIQUE (table_1.id, >>table_2.xxx) >> >> > >Postgres doesn't support database constraints at this time which is >what you would need to do this simply. > > Well, a simple way was to have a table_1-foreign key in the table 2. I just thought since there is: table_1 <--1:n-- table_x <--1:n-- table_2 and I hoped not having to add table_1 <--1:n-- table_2 just to have uniquness of an attribute of table_2 and the key of table_1 >You can enforce this constraint by creating a third table > That is more efford than to stuff the column into table_2. Thanks anyway ;)
I can't see your problem. UNIQUE (cp_id, stall_no) will inforce what you want. Unless, you want to use the same customers table to show who visited which stall. In that case you need another relation. You can't have have a stall rented by and stall visited by at the same table. Andreas wrote: > Jean-Luc Lachance schrieb: > >> Do you really need MANY-TO-MANY between customers and projects? >> I can see customers owning many projects, but do you really have >> projects belonging to many customers? > > > In this case yes. > projects ( > 1, 'x-fair 2003'; > 2, 'y-fair 2003'; > 3, 'x-fair 2004') > > customer ( > 1, 'X ltd'; > 2, 'Y'; > 3, 'Z') > > maybe all 3 have a stall on project 1. > c1 and c2 attend project 2 where c2 has 2 stalls one inside and one > outside the hall. > > I have the cust_project relation to tie other objects like proposals and > letters to something small and common for all the communication to a > customer. > >> If not, fold cust_proj into projects. >> Otherwise, UNIQUE (cp_id, stall_no) on stalls should be enough. > > > > cp_id implies uniquness of (customer, project) couples. > So with UNIQUE (cp_id, stall_no) we get > --> UNIQUE (c_id, p_id, stall_no) > This'd be too much. Stall_no is the number of the cubicles the customers > rent. > It needs to be unique within on project so that we can relate on a > specific spot on the area to send visitors when they ask us and we print > those numbers in the flyer. > > >