Hi all,
This is my first post to the Hibernate Forums, so I hope I keep it consise and of benefit.

Hibernate version: 2.1

This isn't a problem with Hibernate - it's a question on how best to implement a common business requirement using Hibernate. I have searched Google, Java forums, the Hibernate doco and Hibernate forums before creating this post.

I'm redeveloping an existing application (written in MS Access) from scratch, using Hibernate / Quartz / Jasper Reports among other things.

My specific question is how best to implement lookup tables (or "code sets" or "reference tables" - I'm not sure of the "correct" name) using Hibernate.

The existing applications has over 80 "simple" lookup tables - a simple numeric bound to a textual value. An example of this might be a country code of "1008" tied to "Australia". Both the numeric and textual values are dictated by government agencies for reporting purposes, and may be changed from time to time. While hard coding as static variables may be good for performance, it doesn't really lend itself to the ability of an admin user to update the values via an admin interface or similar.

In addition to the "simple" lookup tables, there are around another 50 or so "complex" lookup tables. These have a varying number of attributes (not just 1 numeric and 1 textual value), and the datatypes of these attributes vary also.

The lookup tables will be used predominantly to populate combo boxes in a thick desktop client in a 3 tier J2SE app. Web clients are deemed as not having a rich enough client interface for this application.

Most of the simple lookup tables would have less than 20 rows of data, and although they are likely to change infrequently, when they do change, the updated contents should be pushed to the client immediately.

My feeling is that these lookup tables make the ER overly complex - perhaps without needing to. For example, the "simple" lookup tables could be replaced with 2 tables - 1 that defines each code set (id, name, description), and the other that holds the code set values.

However, at runtime, performance will be paramount (isn't it always?). Is there a best way to implement such lookup tables using Hibernate? Would rolling the "simple" lookup tables into the 2 table structure I mentioned alleviate performance hits, or is the performance impact negligable given that the tables are small? Does the 2 table approach have performance implications for creating objects to represent each of the rows, considering that the objects will need to be categorised by the code set name?

Any feedback on this would be most appreciated. I hope that this is a valid query (no pun intended!) that may be of use to others in the future.

Thanks,

Paul C.

Best way, IMO, is to use a UserType, which looks up the reference value in an application-managed cache of all possible values.

Wow that was quick!

Thanks for your advice Gavin - I'll pursue that approach.

Would you be inclined to implement each code set as a seperate table, or in the 2 table approach?

Paul C.