Hi,

We are trying to make the move to using Hibernate in my development team and I was asked today how Hibernate handles "lookup tables".

In essence what they mean is imagine I have a row



In normalization you would not put string values into the status and product columns. Instead you would create 2 tables statuses and products which have id and then the string value once. A foreign key int is then used.

Because we are used to designing databases first we are concerned that Hibernate will not achieve this.

I am confident that it must be able to but I cannot find the specific documentation on such.

If anyone can provide a pointer I would be very grateful.

AC

I'm using Middlegen due to the fact that we create the databaseschema first and these are normalized as well. Afterwards I take Middlegen to create the mappings.
See the following:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 2.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd" >

<hibernate-mapping>
<!--
Created by the Middlegen Hibernate plugin

http://boss.bekk.no/boss/middlegen/
http://hibernate.sourceforge.net/
-->

<class
name="hibernate.ticket.Workflow"
table="workflow"
schema="public"
proxy="hibernate.ticket.Workflow"
>

<id
name="id"
type="java.lang.Integer"
column="id"
>
<generator class="sequence">
<param name="sequence">workflow_id_seq</param>
</generator>
</id>

<!-- associations -->
<!-- bi-directional one-to-many association to Project -->
<set
name="projects"
lazy="false"
inverse="true"
>
<key>
<column name="workflow_id" />
</key>
<one-to-many
class="hibernate.ticket.Project"
/>
</set>
<!-- bi-directional one-to-many association to WorkflowSeq -->
<set
name="workflowSeqs"
lazy="false"
inverse="true"
>
<key>
<column name="workflow_id" />
</key>
<one-to-many
class="hibernate.ticket.WorkflowSeq"
/>
</set>

</class>
</hibernate-mapping>
Hope this will point in the right direction.

No, it is not a normalization. As I understand int is not better than string. This way was used in desktop applications for dynamic data binding ( dynamicaly populated combobox ) as UI helper, but no of normal forms says you must replace string with int.

we're pretty sure that it is normalization .. e.g

unnormalized..



normalized..

I agree to baliukas to the point that none of the NF rules tells you to replace string with a int, but the n to m relations (with do not exsist in a normalized database) can be solved with the approach adcworks is talking about.

How would you achieve this n to m relationship in Hibernate?

I found it with google:
"2NF attempts to reduce the amount of redundant data in a table by extracting it, placing it in new table(s) and creating relationships between those tables."

As I undestand, you are talking about 2NF, but you do not reduce amount of redutant data, you add redudant data, so it is denormalization.

n to m will be <many-to-many> inside the mapping. Personally I use a ref table which contains the id's of the two related tables and an id column as PK.
The mapping for this ref table looks like this:
<class
name="hibernate.ticket.RefRolesRight"
table="ref_roles_rights"
schema="public"
proxy="hibernate.ticket.RefRolesRight"
>

<id
name="refRolesRightsId"
type="java.lang.Integer"
column="ref_roles_rights_id"
>
<generator class="sequence">
<param name="sequence">ref_roles_rights_ref_roles_rights_id_seq</param>
</generator>
</id>


<!-- associations -->
<!-- bi-directional many-to-one association to Right -->
<many-to-one
name="right"
class="hibernate.ticket.Right"
not-null="true"
>
<column name="rights_id" />
</many-to-one>
<!-- bi-directional many-to-one association to Roles -->
<many-to-one
name="roles"
class="hibernate.ticket.Roles"
not-null="true"
>
<column name="roles_id" />
</many-to-one>

</class>
</hibernate-mapping>