Mapping question

mhsie · **Joined:** Sat Aug 04, 2007 8:12 am **Posts:** 4

Hibernate version: 1.2

Please bear with me as I'm very new to the NHibernate world.

We have a legacy database, which all the enumvalues are stored in a table with the conversion between integer value and string value. Please see below for an example

Order table
order_id (int)
status (int)

OrderDetail table
orderdetail_id (int)
order_id (int)
line_no (int)
item_id (int)
status (int)

EnumValues table with 4 fields "Entity", "Attribute", "IntegerValue", "StringValue", below are the values for the Order status field and OrderDetail status field.
"Order", "Status", "0", "Pending"
"Order", "Status", "1", "Open"
"Order", "Status", "2", "Closed"
"OrderDetail", "Status", "0", "Pending"
"OrderDetail", "Status", "1", "Open"
"OrderDetail", "Status", "2", "Shipped"

I thought of moving all the enumvalues into the code, but the enumvalues are heavily used in our data warehouse system, it will be impossible for this table to be resmoved, and I would like to maintain only one set of enumvalues.

What will be the best way to map the relationships between the other classes and the enumvalues?

Thanks

Marvin

pelton · **Posted:** Mon Aug 06, 2007 4:10 pm

I think in this situation I would try to generate Enum's from your translate rows.

So, perhaps you could write a utilty program that would read the rows from EnumValues and generate Enums:

Code:

enum OrderStatus
{
  Pending = 0,
  Open = 1,
  Closed = 2,
}

enum OrderDetailStatus
{
  Pending = 0,
  Open = 1,
  Shipped = 2,
}

Since the utility would read and generate based on the database, you only have to maintain one set of values -- the database.

--Brian

pelton · **Posted:** Mon Aug 06, 2007 4:23 pm

Or, you could go the other way. (Which is what I am doing in one of my projects).

For reporting purposes, I have a table called app_enum. I have a utility that takes an Enum and generates rows in my app_enum table.

So, the reporting tools can join this table and get descriptions, etc.

Either way, I think the solution is to decide which is easier to maintain, the database or code, and then use that to generate the values for the other.

I can provide some code to generate database SQL from .Net enums if you choose to go that route.

mhsie · **Joined:** Sat Aug 04, 2007 8:12 am **Posts:** 4

Thanks for the suggestion! I will do more research on other possibilities first and if all fails, maybe this is what I have to do.

If we use this solution, I see that we will eventually maintaining 2 sets of values, because the values in the EnumValue table rarely gets changed. And if a change does happen, the change will be very small, so it doesn't really make any sense to run the utility to re-generate the whole enumvalues, just change the code and recompile.

I really appreciate your suggestion!

If anyone has any other suggestions, please let me know!

mnichols · **Joined:** Mon Aug 21, 2006 9:18 am **Posts:** 179

I mostly abandon Enums in .NEt and instead implement a ValueObject with static fields as accessors. That way my enumerations can contain more info and also friendly names mapped in my db for things like reports, etc.
Mike