How would an association table help me (assuming I wanted to enforce
foreign key integrity there as well)?
Family
------
Id
Name
Company
-------
Id
Name
Cars
-------
Id
Name
Ownership
--------
Row_id
Owned_by (either company or family)
Owner_id
Car_id
Wouldn't owner_id run into the same problem of being a dual FK
relationship? I suppose I could do:
Ownership
--------
Row_id
Family_id (might be null)
Company_id (might be null)
Car_id (fk --> cars(id))
But then as the number of potential car owning tables expands, my
association table gets arbitrarily large, doesn't it?
Or am I missing something obvious here?
--- Pat
> -----Original Message-----
> From: Leonardo Quijano Vincenzi [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, November 23, 2005 12:07 PM
> To: Tapestry users
> Subject: Re: OT: Stupid SQL Question
>
> AFAIK this is not legal and foreign keys must map to a specific table.
> Usually this case would get handled by an association table.
>
> --
> Ing. Leonardo Quijano Vincenzi
> Director Técnico
> DTQ Software
>
>
> Patrick Casey wrote:
> >
> >
> > Ok, I admit it, this is probably a stupid question, but I
> can't
> > (curiously) seem to find an answer to this in the SQL spec.
> >
> >
> >
> > Is it legal for a child table (the n side of a 0..n)
> > relationship to have a foreign key relationship to *more than one*
> parent
> > table?
> >
> >
> >
> > e.g.
> >
> >
> >
> > is this legit:
> >
> >
> >
> > family
> >
> > ---------
> >
> > id
> >
> > name
> >
> >
> >
> > company
> >
> > -------
> >
> > id
> >
> > name
> >
> >
> >
> > cars
> >
> > --------
> >
> > id
> >
> > owner_class (either family or company)
> >
> > owning_id (foreign_key company(id)), foreign_key
> family(id))?
> >
> >
> >
> > In that either a family, or a company, might potentially own
> a
> > car?
> >
> >
> >
> > I'm actually not using cars, of course, it's an attachment
> table
> > that currently all my tables share (any attachment to any object, gets
> > stuffed into attachment).
> >
> >
> >
> > Naturally, mySQL lets me get away with it, but then mySQL
> lets
> > you get away with *anything* so my question is, should I refactor this
> now
> > on the expectation that some day I may need to use a real database? Or
> is
> > having multiple foreign key constraints on the same column copasetic
> under
> > the ANSI spec?
> >
> >
> >
> > --- Pat
> >
> >
> >
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
