Entity engine, "many" relations, foreign keys

[Fabian Gorsler]

i,

yes, I found another thing I'm a bit confused about. ;)

I found something strange while writing a little tool for generating
a entitymodel.xml from existing tables in a database. I think modelling
tables/entities with a nice tool for DB design and putting the relations
into the database is much easier than editing the whole XML thing by
hand. The tool works well and I hope I'll be allowed for offering this
tool to you - perhaps as an part of OFBiz - but before that the dirty
hack has to become some nice part of software and I have to ask my boss,
whether I'm allowed to release it under the Apache license as OSS.

Back to my question: Why does the entity engine not define foreign
keys in the database for relations of the type "many"? I have been
thinking about that for about half an hour but I haven't got it. Is
this perhaps a problem with my DB or my version of OFBiz (trunk from
last week monday).

For example:
I have a table with some persons and I want to store more than one
address per person. For that I would define at least two entities.

EntityA: Details about the person, with a personId as primary key.
EntityB: Some addresses with a constructed primary key with addressId
           and personId.

In this example the relation for personId had to be "many". I've tested
this already with a simple entity configuration, but the entity
engine does not create a foreign key for personId on the relation
entity_b. Is there any background cause for that behavior I didn't get?
A constraint violation won't happen, because the column isn't set to
UNIQUE or anything similar (Did I overlook s.th. like that?)


TIA :)

Best regards,
Fabian.

[David E Jones-2]

Foreign keys are done for type "one" relationships, not type many. A type "many" relationship is usually just the reverse direction of a type "one" relationship so the FK covers both.

What would it mean to have a foreign key on a type "many" relationship?

-David


Fabian Gorsler wrote:

> i,
>
> yes, I found another thing I'm a bit confused about. ;)
>
> I found something strange while writing a little tool for generating
> a entitymodel.xml from existing tables in a database. I think modelling
> tables/entities with a nice tool for DB design and putting the relations
> into the database is much easier than editing the whole XML thing by
> hand. The tool works well and I hope I'll be allowed for offering this
> tool to you - perhaps as an part of OFBiz - but before that the dirty
> hack has to become some nice part of software and I have to ask my boss,
> whether I'm allowed to release it under the Apache license as OSS.
>
> Back to my question: Why does the entity engine not define foreign
> keys in the database for relations of the type "many"? I have been
> thinking about that for about half an hour but I haven't got it. Is
> this perhaps a problem with my DB or my version of OFBiz (trunk from
> last week monday).
>
> For example:
> I have a table with some persons and I want to store more than one
> address per person. For that I would define at least two entities.
>
> EntityA: Details about the person, with a personId as primary key.
> EntityB: Some addresses with a constructed primary key with addressId
>           and personId.
>
> In this example the relation for personId had to be "many". I've tested
> this already with a simple entity configuration, but the entity
> engine does not create a foreign key for personId on the relation
> entity_b. Is there any background cause for that behavior I didn't get?
> A constraint violation won't happen, because the column isn't set to
> UNIQUE or anything similar (Did I overlook s.th. like that?)
>
>
> TIA :)
>
> Best regards,
> Fabian.

[Fabian Gorsler]

Hi David,

David E. Jones wrote:
> What would it mean to have a foreign key on a type "many" relationship?

exactly this was the question I asked myself when I've modelled the
entities. Well, the problem was, OFBiz forced me to use "many". It
didn't accept "one" - the entity was not created with "one".

I'm now at home and have no access to the files (entitydef.xml and
logs), but I will send them tomorrow to the list.

Best regards,
Fabian.

[Fabian Gorsler]

Hi David,

this morning arriving in office I saw the problem. Gee, so stupid...
Imagine a typo and creating the file with C'n'P... Damn... It looked
really nice consistent...

Well, the error is corrected now, but thanks for explaining the "one"
/"many" thing. It helped me a lot while "debugging" my entitydef.xml.

Best regards,
     the tomato-eyed
     Fabian.