Thanks for the suggestion; unfortunately the specific example I gave
here was somewhat contrived for simplicity's sake. The actual data model
involves an attachments table that any gui-visible object is linked to (so
that you can put attachments on workorder, users, expense lines, etc).
There's really no logical relationship between the possible parents.
So refactoring so that everything descended from a single root
"graphical_object" while very smalltalky would, I think, murder my
performance as not only would the root table get hugely large, but
retrieving anything in the system would require a minimum of one join.
In any event, I just bit the bullet and refactored to use a join
table per parent, which, apart from the 20 minutes required to write the
script to change all my xml files, seems to have just fing-magically worked.
Score one for hibernate I suppose :).
--- Pat
> -----Original Message-----
> From: news [mailto:[EMAIL PROTECTED] On Behalf Of Javier Molina
> Sent: Wednesday, November 23, 2005 2:19 PM
> To: [email protected]
> Subject: Re: OT: Stupid SQL Question
>
> Depending on your model, you might be able to refactor Family and
> Company to descend from a common class, let's say, Contact, such that
>
> class Contact {
> long id;
> String name;
>
> [getters and setters]
> }
>
> class Family extends Contact {
> ...
> }
>
> class Company extends Contact {
> ...
> }
>
> class Car {
> Contact owner;
> }
>
> You would then have tables:
>
> - contacts (id,name) primary key(id)
> - families (id,<additional fields specific to class Family>) primary
> key(id), foreign key contacts(id)
> - companies (id,<additional fields specific to class Company>) primary
> key(id), foreign key contacts(id)
>
>
> and change the hibernate mapping to use a table-per-subclass mapping.
>
> To me, this looks like a cleaner model, although it will require
> additional selects to join the families and companies when you read a car.
>
> Another option, if your fields are nullable, is the table per class
> hierarchy mapping, with a single table for both Family and Company, but
> all non-inherited fields will cannot be marked not null.
>
> Once you decide to create an inheritance hierarchy, there are several
> mapping options, see the Inheritance mapping chapter on the
> documentation and/or Hibernate In Action.
>
> Patrick Casey escribió:
> >
> >
> > 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]
