You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@tapestry.apache.org by Patrick Casey <pa...@adelphia.net> on 2005/11/23 21:03:49 UTC
OT: Stupid SQL Question
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
Re: OT: Stupid SQL Question
Posted by Ron Piterman <rp...@gmx.net>.
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:news@sea.gmane.org] 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: tapestry-user-unsubscribe@jakarta.apache.org
>>For additional commands, e-mail: tapestry-user-help@jakarta.apache.org
>
>
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: tapestry-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: tapestry-user-help@jakarta.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: tapestry-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: tapestry-user-help@jakarta.apache.org
RE: OT: Stupid SQL Question
Posted by Patrick Casey <pa...@adelphia.net>.
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:news@sea.gmane.org] 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: tapestry-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: tapestry-user-help@jakarta.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: tapestry-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: tapestry-user-help@jakarta.apache.org
Re: OT: Stupid SQL Question
Posted by Javier Molina <nu...@myrealbox.com>.
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: tapestry-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: tapestry-user-help@jakarta.apache.org
RE: OT: Stupid SQL Question
Posted by Patrick Casey <pa...@adelphia.net>.
I actually am using hibernate to create my data model (I'm actually
pretty experienced at data modeling, the problem is that I'm largely self
taught so I have the occasional glaring blind spot like not knowing the
precise rules of foreign keys :)).
The problem is that I told hibernate to use one association table
for all the parent tables which hibernate thinks is legal, but which the
database layer screams bloody murder at. The other problem is that I
prototyped the model in myISAM, which is beyond loosey-goosey in terms of
sql standards, so the "multiple foreign keys on the same row" approach
passed muster there. Now that I'm actually getting closer to rollout I'm
trying to swap in INNODB in back, only it won't accept the create table DDL
because of the FK duplication issue.
I was hoping somebody had a solution other than "one association
table per mapping" if for no other reason than that'll involve a major
refactor of a lot of mapping files :(.
Oh well, looks like I've got plans for my thanksgiving weekend now
:).
Thanks for the suggestions,
--- Pat
> -----Original Message-----
> From: Henri Dupre [mailto:henri.dupre@gmail.com]
> Sent: Wednesday, November 23, 2005 1:21 PM
> To: Tapestry users
> Subject: Re: OT: Stupid SQL Question
>
> On 11/23/05, Patrick Casey <pa...@adelphia.net> 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?
>
> I'm not sure if this could help you but if you are used rather writing
> code, you may try to model all your data as Java object and use
> Hibernate + hibernate tools to generate your schema. I think it would
> do a nice job especially concerning all relational stuff.
> SQL is sometimes quite unfriendly.
>
> Thanks,
>
> Henri.
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: tapestry-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: tapestry-user-help@jakarta.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: tapestry-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: tapestry-user-help@jakarta.apache.org
Re: OT: Stupid SQL Question
Posted by Henri Dupre <he...@gmail.com>.
On 11/23/05, Patrick Casey <pa...@adelphia.net> 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?
I'm not sure if this could help you but if you are used rather writing
code, you may try to model all your data as Java object and use
Hibernate + hibernate tools to generate your schema. I think it would
do a nice job especially concerning all relational stuff.
SQL is sometimes quite unfriendly.
Thanks,
Henri.
---------------------------------------------------------------------
To unsubscribe, e-mail: tapestry-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: tapestry-user-help@jakarta.apache.org
Re: OT: Stupid SQL Question
Posted by Philip Lopez <ta...@illanah.com>.
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:leonardo@dtqsoftware.com]
> > 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: tapestry-user-unsubscribe@jakarta.apache.org
> > For additional commands, e-mail: tapestry-user-help@jakarta.apache.org
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: tapestry-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: tapestry-user-help@jakarta.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: tapestry-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: tapestry-user-help@jakarta.apache.org
Re: OT: Stupid SQL Question
Posted by Leonardo Quijano Vincenzi <le...@dtqsoftware.com>.
There are several approaches (I think in the Hibernate project there are
better explanations from a OO point of view), but I can think of the
following:
1) Association 'dispatcher' table
Ownership
-------------
Row_id
Owner_type
Family_id
Car_id
...
etc
With this approach, as you say, we add columns for every kind of owner
to the Ownership table. It's like a "dispatcher" table, and of course,
if the number of possible owners is very large it drags down (though a
range of 1-15 owner types would be acceptable, IMO)
Or... we could do it backwards:
2) Foreign keys on owner tables
Family
--------
Id
Name
car_id
Company
------------
Id
Name
car_id
Car
----
Id
Name
This doesn't define an "ownership" per se, but allows for lots of owner
types.
The "owners for this car" query gets a little complicated, of course.
There could be other approaches, of course. Weak foreign keys also comes
to mind (a single Owner table with "table" and "id" properties).
--
Ing. Leonardo Quijano Vincenzi
Director Técnico
DTQ Software
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
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: tapestry-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: tapestry-user-help@jakarta.apache.org
RE: OT: Stupid SQL Question
Posted by Patrick Casey <pa...@adelphia.net>.
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:leonardo@dtqsoftware.com]
> 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: tapestry-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: tapestry-user-help@jakarta.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: tapestry-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: tapestry-user-help@jakarta.apache.org
Re: OT: Stupid SQL Question
Posted by Leonardo Quijano Vincenzi <le...@dtqsoftware.com>.
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: tapestry-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: tapestry-user-help@jakarta.apache.org
Re: OT: Stupid SQL Question
Posted by Alan Chandler <al...@chandlerfamily.org.uk>.
On Wednesday 23 Nov 2005 20:03, 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?
>
I can't answer you specifically - but I did a lot of datamodelling in the past
and with this situation you define that car and family are subclasses of an
owner entity
Looking at my postgres documentation there is an INHERITS clause in CREATE
TABLE, which (provided it is single inheritence) seems to be part of the SQL
standard.
--
Alan Chandler
http://www.chandlerfamily.org.uk
Open Source. It's the difference between trust and antitrust.
---------------------------------------------------------------------
To unsubscribe, e-mail: tapestry-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: tapestry-user-help@jakarta.apache.org
Re: OT: Stupid SQL Question
Posted by Henri Dupre <he...@gmail.com>.
On 11/23/05, Patrick Casey <pa...@adelphia.net> 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.
I just looked more in details on your issue
you have
owning_id (foreign_key company(id)), foreign_key family(id))?
Why do you have this as an id?
I don't believe there is anything wrong of having in the table cars
two foreign keys and where the column can be NULL. As an ID though, it
doesn't make sense that they could be null. I don't believe so much in
the one association table theory because then you end up with really
lots of extra unecessary code to solve what is a very simple problem.
We are actually not using any SQL foreign key constrain on our
InnoDB... I let hibernate take care of the constraint. I find it
getting too heavyweight with many foreign keys + hibernate.
Henri.
---------------------------------------------------------------------
To unsubscribe, e-mail: tapestry-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: tapestry-user-help@jakarta.apache.org
RE: Stupid SQL Question
Posted by Ashish Raniwala <ar...@gmail.com>.
I feel it is very legal to create these types of relationships using join
table. So you can create family_car and company_car tables to create the
relationship.
-----Original Message-----
From: Patrick Casey [mailto:patcasey@adelphia.net]
Sent: Wednesday, November 23, 2005 12:04 PM
To: 'Tapestry users'
Subject: OT: Stupid SQL Question
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: tapestry-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: tapestry-user-help@jakarta.apache.org