From: Grigoriy G. Vovk <grigoriy.vovk@linustech.com.cy>
To: Denis Bucher <dbucher@niftycom.com>
Cc: PGSQL-SQL <pgsql-sql@postgresql.org>
Subject: Re: Are circular REFERENCES possible ?
Date: Tue, 7 Aug 2001 13:16:47 +0300 (EEST)
Message-ID: <20010807125948.H931-100000@callisto.internal.linustech.com.cy> (raw)
In-Reply-To: <5.1.0.14.0.20010807114609.00a18490@mail.niftycom.com>
Aug 7, 11:54 +0200, Denis Bucher wrote:
> We deliver to the *shops* of our *customers*.
> We have therefore two tables :
> - customers (enterprise, financial information, and so on...)
> - shop (with a name, street, phone number, name of manager)
>
> Now, each shop REFERENCES a customer so that we know
> to which customer belongs a shop.
>
> AND, each customer has a DEFAULT shop for deliveries, i.e. most
> customers only have one shop, or a main shop and many small ones.
> Therefore a customer should REFERENCES the 'main' or 'default' shop.
>
> Which leads to :
>
> CREATE TABLE shops ( id_shop SERIAL PRIMARY KEY, id_cust integer REFERENCES
> customers, .......)
> CREATE TABLE customers ( id_cust SERIAL PRIMARY KEY, id_defaultshop integer
> REFERENCES shops, .......)
>
> But this doesn't work ! Postgres complains like "ERROR: Relation
> 'customers' does not exist"
> when creating 'shops'.
>
> Someone told me I should create a third table, ok, but in this case I loose
> the total
> control about my logic... Do you have a suggestion ?
We have next conditions:
Each customer has many shops;
Each shop belong to one and only one customer;
So, you have a classic one -> many relation.
Next. Shop may have additional attribute - 'default'.
You can add column 'default bool' to the table 'shops' but then you should
implement something like trigger to check that only one shop of each
customer will have 'default' = 't';
Of cause, you can create third table like
create table default_shop(id_shop int not null primary key references
shops);
but it doesn't make a sense, you again must implement something to keep
your business logic like trigger which will check that only one shop from
belong to a customer there is in the table.
So, I think, best solution is add column 'default bool' in the table
'shop' and create a trigger before insert on shop which will check if
inserted row has 'default' ='t' are there a row which already has 'default'
= 't'. May be you will want a trigger for update. And may be for delete -
if will deleted 'default' shop - what we should to do?
Better will be create a function for delete shop which will take id_shop
which will be deleted and id_shop which will be default as arguments and
this function will delete one shop and assign an other as default.
my best regards,
----------------
Grigoriy G. Vovk
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: grigoriy.vovk@linustech.com.cy, dbucher@niftycom.com
Subject: Re: Are circular REFERENCES possible ?
In-Reply-To: <20010807125948.H931-100000@callisto.internal.linustech.com.cy>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox