Re: Enforcing one-to-many relationships

From: Frank van Bortel <frank.van.bortel_at_gmail.com>
Date: Sun, 08 Jul 2007 15:32:27 +0200
Message-ID: <f6qp18$poj$1@news1.zwoll1.ov.home.nl>

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Vladimir M. Zakharychev wrote:
> On Jul 7, 11:34 pm, "John" <gleneldon2000-oraclemi..._at_yahoo.co.uk>
> wrote:

>> <fitzjarr..._at_cox.net> wrote in message
>>
>> news:1183834858.545940.161150_at_k79g2000hse.googlegroups.com...
>>
>>
>>
>>> On Jul 7, 11:14 am, "John" <gleneldon2000-oraclemi..._at_yahoo.co.uk>
>>> wrote:
>>>> What's the best way to enforce a one-to-many relationship?  It's not a
>>>> zero-to-many relationship - I need at least one entry in the child table.
>>>> Thank you for any advice
>>>> John
>>>> (I don't want replies from Sybrand Bakker)
>>> Too bad, really, as you're throwing away an excellent source of
>>> information.
>>> That being the case I'll tell you that which you don't want to hear:
>>> You need to write your application to NOT allow unreferenced parent
>>> records.  Oracle won't do that for you.  Yes, you can set the foreign
>>> key columns to NOT NULL and require a valid entry, however that does
>>> NOT force Oracle to require a referencing record in the child table; a
>>> NULL record is not the same as a  NULL foreign key value.  You could
>>> also use a trigger to populate a 'dummy' record in the child table,
>>> but that can get complicated and can result in numerous 'dummy'
>>> records if multiple users are allowed to use this application
>>> simultaneously.  Likewise with your application code, although if
>>> you  process this 'dummy' record on insert of the parent you'll stand
>>> a better chance of success.
>>> What, exactly, is the business case for this 'logic'?  There must be
>>> one.
>>> David Fitzjarrell
>> Thank you, that's very useful.  I've had a suggestion of how a materialized
>> view can be used.  I'll let you know if it works without major performance
>> problems - but we'll probably go with the enforcement being on the
>> application side as you suggest.
>>
>> A very brief description of the business case is that all our "items" need
>> at least one manufacturer.
>>
>> John

>
> In this case your relationship is wrong and should be reversed: with
> your current model there's going to be many instances of one
> manufacturer referencing one or more items it produces. This model
> forces you to duplicate manufacturers in the table of items over and
> over, and if you delete all items ever produced by a particular
> manufacturer, you will lose information on that manufacturer itself.
> This model obviously violates 2NF. Items depend on their
> manufacturers, not vice-versa.
>
> What you need is a set of manufacturers where each is listed only
> once, and a set of items referencing the set of manufacturers through
> a foreign key, where each item produced by particular manufacturer is
> listed only once.
>
> Regards,
> Vladimir M. Zakharychev
> N-Networks, makers of Dynamic PSP(tm)
> http://www.dynamicpsp.com
>

No - the model is sound; relationships can and should always be described from either endpoint:

Manufacturers -> Items:
each manufacturer delivers none, one or more items. ** Optionality in the relation.
 You may have a manufacturer, not delivering any items.

(Some modelers use: each manufacturer may deliver one or more items. in that case, the "may" indicates the optionality of the relationship)

Item -> Manufacturers:
each and every item is delivered by at least one manufacturer **Mandatory relation! You cannot have items, delivered by John Does. You can have items, delivered by more that one manufacturer.

The case described here does not allow for the optionality in manufacturers. Oracle indeed does not check that. It does check the existence of child records (items) when trying to remove a parent (manufacturer) or the presence of the parent, when referring to it from the child (if a manufacturer is referenced, it must exist)
- --
Regards,
Frank van Bortel

Top-posting is one way to shut me up...
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (MingW32)

iD8DBQFGkOdrLw8L4IAs830RApedAJ9gahUqkCwRvsB7B1gpWWI9qVhoVgCePTPu ryqRUO7gbJTVwRsFOaRsMfk=
=FIRh
-----END PGP SIGNATURE----- Received on Sun Jul 08 2007 - 08:32:27 CDT