hi,

i have a legacy application that stores a category hierarchy using two tables:

CATEGORIES:
- CATEGORY_ID (PK)
- CATNAME
- TYPE_ID

CATEGORY_HIERARCHY:
- PARENT_CATEGORY_ID
- CHILD_CATEGORY_ID

it's ugly; multiple parentage is allowed and there's no PK on the category_hierarchy table. the main reason it was designed in this way was to support oracle's "connect by prior" mechanism for hierarchical data.

what i'd really like is to map both tables to a single category object -- one that encapsulates both basic info (type, name, id) and hierarchy info. furthermore, it would be nice to have getChildren() and getParents() methods that return Category objects.

i'm not sure this is possible given these tables, but i present some ideas below using what i know.

1. i created a CategoryHierarchy transfer object that just has the 2 fields of the category_hierarchy table.

2. the Category.java class, which is the transfer object for the categories table, has the basic fields of the categories table and 2 sets for parents and children:



2. the Categories.hbm.xml file maps the child and parent sets to the category_hierarchy table. for the children, the foreign key is the parent_category_id; for the parents, the child_category_id is the foreign key.



am i on the right track? or should i just consider using simple transfer objects for each table and resolve the association using other methods?

thanks,

Mike

Hi mcopenha,

Amazingly, this is the same problem I'm having. Other projects i've worked on have encoded this relationship with a 'self-reference' in the entity, instead of the association table, and mapping it is straight forward.

Our mappings look very similar (and probably wrong; help here hibernate experts?). However, I used <many-to-many> since the schema had a two-column link table composed of the 'parent' and 'child' account id's. That's about all I have...seems like I need a schema change but may not be possible.

<!-- Child Accounts -->
<set name="children" lazy="true" cascade="save-update" table="`AccountHierarchy`">
<key column="`ParentAccountID`"/>
<many-to-many class="com.hostingmatrix.account.Account" column="`AccountID`"/>
</set>
<!-- Parent Account -->
<set name="parent" lazy="true" inverse="true" cascade="save-update" table="`AccountHierarchy`">
<key column="`ChildAccountID`"/>
<many-to-many class="com.hostingmatrix.account.Account" column="`AccountID`" unique="true"/>
</set>

- James