Re: Enforcing one-to-many relationships

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 Received on Sun Jul 08 2007 - 05:31:15 CDT