Do you need to perform the "to which entity (object) does this car (attachment) belong?" query, or is the inverse much more common, i.e. "which cars (attachment) does this family/corporate/etc. (page entity) own?"
If the latter is the case, then the obvious model, to my mind, is for there to an association table per owner type, i.e. family_cars, company_cars, and so forth. An ORM tool like Hibernate can be used to provide the query, e.g. myFamily.getCars() with minimal hassle. If there are *lots* of car (attachment) owners, there will obviously be *lots* of association tables, however. An alternative might be to look at using some sort of "AssetOwner" interface and implement an inheritance strategy. There are a few different options here, which start to mirror the sorts of solutions already put forward, but perhaps with improved encapsulation. Check out http://www.hibernate.org/hib_docs/reference/en/html/inheritance.html for some alternatives. On Thu, 24 Nov 2005 06:14, Patrick Casey wrote: > 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] --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
