Re: Implementing 0:1 relationships

From: Frank van Bortel <f.van.bortel_at_vnl.nl>
Date: Fri, 22 Oct 1999 10:20:50 -0700
Message-ID: <38109CF2.16E97C29@vnl.nl>

Eric Spear wrote:

> I think option 2 is best.
>
> Martin Trzaskalik wrote:
>
> > Is there a general rule how to implement 0:1 relationships,
> > e.g. an entry in TABLE A might but does not have to reference
> > an entry in TABLE B.
> >
> > I am thinking of the following alternatives:
> >
> > 1.)
> > A (A_ID, NAME, B_ID)
> > B (B_ID, TEST)
> > where A.B_ID might be NULL or a foreign key to B
> >
> > 2.)
> > A (A_ID, NAME)
> > B (B_ID, TEST)
> > A_B (A_ID, B_ID)
> > where A_B.A_ID and A_B.B_ID are foreign keys to A and B.
> >
> > The application would consist of outer joins involing A and B.
> >
> > Which alternative is better in aspect of performance etc?
> >
> > Martin

If you really mean an optional 0:1 relationship, it should be modeled as

one or more optional columns (NULLs allowed). What is descried here, are optional 1:M relationships.

--
Met vriendelijke groet/kind regards,

Frank van Bortel
Technical consultant Oracle

Work:                                Home:
----------------------------------   ----------------------------
Inter Access V&L                     Hunzestraat 4
Palatijn 3, 7521 PN Enschede         7555 WB Hengelo
PoBox 545, 7500 AM Enschede          (31)074-2425046

053-4341500 Received on Fri Oct 22 1999 - 12:20:50 CDT