Re: OT: Stupid SQL Question

[Ron Piterman]

Just an idea:
On the database side, you should use inheritance, just like Javier 
Molina or Alan Chandler suggested.
On hibernate side, if adding a hibernate subclass mapping for each table 
is not pragmatic, you can break the hibernate relationships and use 
normal properties, using a service to supply the related object.
Cheers,
Ron

Patrick Casey wrote:
        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: tapestry-user@jakarta.apache.org
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]




---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]