Hibernate best practices

[Todd Farmer]

I have a Hibernate application that supports a fairly standard publishing workflow. An end user provides information and submits it for review. Authorized users review, validate and correct the user-supplied data and publish the approved data. While the database schema and the object model for each step is identical, they have to be persisted independently so that one can be changed without affecting others. For example, a user can be modifying the user-supplied data, but this data can't affect that which has already been approved and published without being pushed through the entire process.

My initial database model has a series of three tables with identical columns. For example, a user supplies information on a company, and it is entered into the COMPANY_INITIAL table. When the user submits the company profile, the contents are copied to the COMPANY_REVIEW table. Authorized users make changes as needed (pushing changes back to the COMPANY_INITIAL table is an issue), and once the profile is approved, it migrates to the COMPANY_PRODUCTION table.

OK, now for the question.

I'm trying to determine the best practices approach to dealing with these virtually identical domain objects and database tables. My first attempt feels like a hack. I've used Hibernate's support for naming strategies to reuse the domain objects against the different tables. Of course, this means I have to have multiple sessions (one for each naming strategy). And, of course, to make matters worse, there are tables that do not follow that naming strategy, so I have to hack the naming strategy itself to not modify those table names.

I know I could duplicate the mapping documents and subclass the Company domain object for the _REVIEW and _PRODUCTION stages, but then I've got a lot of duplicate code (and mapping documents). That doesn't seem right, either.

I've thought about migrating to a database schema where all versions of a profile are stored in one table (COMPANY) and there is a discriminator that tells me what state a particular row is in. I'm not sure that's the right solution, and it would require a number of changes to the existing data structure and code.

Any thoughts or guidance on best practices principles or Hibernate concepts that would help out here?

Thanks,

Todd Farmer

[Scott Ambler]

My advice would be to refactor the database schema as it sounds as if you have a design problem with your schema: in other words, fix the actual problem, don't put a band aid on it.

You might find The Process of DB Refactoring interesting.

- Scott

[Todd Farmer]

Scott,

Thanks for the reply and for the link. I appreciate the pointers on how to get from where I am today to where I should be/want to be, but I'm still looking for some guidance on determining what a good data model would look like for my scenario. I'd love pointers to tutorials, books or other resources that you may recommend that would help me define an appropriate data model to migrate to.

Thanks!

Todd Farmer

[Mark Spritzler]

well, for the identical tables, I'd combine that to one table and add just one field to determine the record type.

Mark

[Todd Farmer]

That's what I was afraid of. That means I probably need to create a new PK, since my current PK is specific to a company (company_id). Company_id then likely becomes a FK to some other table (that has a PK of company_id and attributes of Company that are static in nature, maybe name).

The good thing is that I am using Hibernate, so changing those relationships will be easy...in that application. Changing it in the Perl legacy code will be less fun.

Thanks for the suggestion!

Todd Farmer