Complex table structure problem?

saschwen · **Joined:** Thu Apr 14, 2005 9:38 am **Posts:** 17

Hello,

I'm a newbie to hibernate and I have a question about how I would go about accomplishing something.

We have a content management system that uses oracle to store data. The basic table structure consists of 1 +n tables for then number of different content types. e.g. Article, Image, Collection, Link,...

The first table would have the columns id, assettype, and pubid. The pubid is a pointer to which site the asset belongs to. The assettype column tells me what table the asset data is in and the id column is the id of the asset i need to load from the table. for example if assettype is equal to Article then I would do a select from the article table with the id.

Is there a way to have hibernate do the work for this? There are approximately 10 - 15 different assettypes and it would be a lot of work and code if I had to first load the asset definition and then figure out what set of hibernate classes to use from there to load the asset data.

Thank you,

Steve.

pksiv · **Posted:** Wed Feb 08, 2006 4:16 pm

saschwen wrote:

Hello,

I'm a newbie to hibernate and I have a question about how I would go about accomplishing something.

We have a content management system that uses oracle to store data. The basic table structure consists of 1 +n tables for then number of different content types. e.g. Article, Image, Collection, Link,...

The first table would have the columns id, assettype, and pubid. The pubid is a pointer to which site the asset belongs to. The assettype column tells me what table the asset data is in and the id column is the id of the asset i need to load from the table. for example if assettype is equal to Article then I would do a select from the article table with the id.

Is there a way to have hibernate do the work for this? There are approximately 10 - 15 different assettypes and it would be a lot of work and code if I had to first load the asset definition and then figure out what set of hibernate classes to use from there to load the asset data.

Thank you,

Steve.

look at the <joined-subclass> mapping in the docs. It would be something like this -

Code:

<hibernate-mapping>
  <class name="BaseObject" table="BASE_TABLE">
    <id name="id" column="ID" type="long">
      <generator..../>
    </id>
    
    <many-to-one name="Site" column="pubid"/>
    
    <joined-subclass name="Article" table="ARTICLES">
      <key column="ARTICLE_ID"/> <!-- or whatever the column name is in the article table. -->
      <!-- article specific columns go here'
    </joined-subclass>       
    
    <joined-subclass name="Image" table="IMAGES" >
      <key column="IMAGE_ID"/>
      <!-- image specific columns go here'
    </joined-subclass>    
    
    <joined-subclass name="Collection" table="COLLECTIONS" >
      <key column="COLLECTION_ID"/>
      <!-- collection specific columns go here'
    </joined-subclass> 
    
    <joined-subclass name="Link" table="LINKS">
      <key column="LINK_ID"/>
      <!-- collection specific columns go here'
    </joined-subclass>  
    
    ....
    ....more subclasses...
    ....        
  </class>  
</hibernate-mapping>

saschwen · **Joined:** Thu Apr 14, 2005 9:38 am **Posts:** 17

Hey pksiv,

Thanks for the reply. By the looks of it, you are suggesting that I use a separage id column for each asset type. The problem with our current system is that it uses 1 id column that points to all the tables. Actually it uses 2 columns in a combination to determine where the asset data resides.

Take this example table

Table name: AssetPublication
Columns: id,type
row1: 1,Page
row2: 2,Article
row3: 3,Article
row4: 4,Image

In row1 the type says Page. That means we load the data from the Page table with the id 1. In row2 type says Article which means I load the data from the Article table with the id of 2 and so on....

Is there a way I can modify what you mentioned to have hibernate use a combination of 2 columns to define how the tables link together?

Thank you,

Steve.

pksiv · **Posted:** Wed Feb 08, 2006 9:42 pm

Maybe I misunderstood your problem but the example I provide assumes there is one table that contains the base information

Code:

BASE_TBL
id
assettype
pubid

and then for each "asset type" a separate table that contains the type specific data.

Code:

ARTICLES
article_id <-- The same ID value from the base table
article_info
...
...

IMAGES
image_id <-- The same ID value from the base table
image_size 
...
...
etc...

The Key column of the asset-specific table is specified in the mapping of that joined-subclass. This type assumes that it's joined to the ID of the base table.

saschwen · **Joined:** Thu Apr 14, 2005 9:38 am **Posts:** 17

Hey pksiv,

I think I get it now. I wasn't quite getting what the xml fields meant but I think I do now.

Thank you,

Steve.