Re: Re: Are circular REFERENCES possible ?

Hi Mike,

A few people have suggested this, but the thing I don't like (and I think at 
some point William has also stated this) is that doing it this way, you lose 
the logic (and the purity) of the data.  If you allow the cyclic reference, 
then the data behaves -and looks - exactly as it should do. The customer 
refers to the shop, and the shop refers to the customer.

If I remember correctly, one of the cardinal rules of normalising data is 
that all related data (e.g. customer) should be together (one table) - hense, 
the default_shop belongs to the customer table
.  
Relationship table should only be used for n-to-n links.

Gary

On Tuesday 14 August 2001  2:16 pm, Michael Ansley (UK) wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> The easiest way out is probably to create a relationship entity
> called 'default' between customer and shop for the default
> relationship.  This way you only have to have one direct
> relationship, because the other is expressed through the 'default'
> table.
>
> Just a thought...
>
>
> MikeA
>
> >> -----Original Message-----
> >> From: William Courtier [mailto:wcourtier@travelprice.com]
> >> Sent: 07 August 2001 11:10
> >> To: pgsql-sql@postgresql.org
> >> Subject: [SQL] Re: Are circular REFERENCES possible ?
> >>
> >>
> >> I denis,
> >>
> >> I don't know if you can use a circular REFERENCES, but why
> >> you try made a
> >> references before the table is created (customers). You
> >> should create the
> >> references after and use the foreign key if circular
> >> references does not
> >> work.
> >>
> >> William
> >> "Denis Bucher" <dbucher@niftycom.com> a écrit dans le message
> >> news: 5.1.0.14.0.20010807114609.00a18490@mail.niftycom.com...
> >>
> >> > Hello !
> >> >
> >> > I have a case where I wanted to do circular REFERENCES, is this
> >> > impossible ?
> >> >
> >> > Just an example where it would be useful :
> >> >
> >> > 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 ?
> >> >
> >> > Thanks a lot in advance !
> >> >
> >> > Denis
> >> >
> >> >
> >> > ---------------------------(end of
> >>
> >> broadcast)---------------------------
> >>
> >> > TIP 1: subscribe and unsubscribe commands go to
> >>
> >> majordomo@postgresql.org
> >>
> >>
> >>
> >> ---------------------------(end of
> >> broadcast)---------------------------
> >> TIP 2: you can get off all lists at once with the unregister
> >> command
> >>     (send "unregister YourEmailAddressHere" to
> >> majordomo@postgresql.org)
>
> -----BEGIN PGP SIGNATURE-----
> Version: PGPfreeware 6.5.3 for non-commercial use <http://www.pgp.com>
>
> iQA/AwUBO3kkqnympNV/C086EQKcWgCfd1Z2Hbi/g7Rj633Myj67HxkjgvkAn1n+
> hXvHqca0bqE73XY4tmjDq/7v
> =2nf2
> -----END PGP SIGNATURE-----

----------------------------------------
Content-Type: text/html; charset="iso-8859-1"; name="Attachment: 1"
Content-Transfer-Encoding: quoted-printable
Content-Description: 
----------------------------------------

-- 
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000