Hi everybody!
This is more of a general system/db design question, but I was wondering if any of you had the chance to tackle this. Maybe even with Hibernate. Hope you have some time to chat on this. Now, i must warn you, I am a newbie so don't get mad on me :) I did searched before asked.

I want to offer some "extended properties" for an entity and I'm not sure what is the best way as performance/scalability.

So, what does "extended property" mean to me? Here's an example. I have the User entity. This has some basic properties as "username", "email" and "password". Now, at some point, I might need to add some unexpected properties. Let's say that I need to add the "age" and "gender" properties. Instead of changing my database structure and classes, I could have 2 entities: CustomProperty and CustomPropertyValue. The relation between CustomProperty and CustomPropertyValue is 1..1 and the relation between User and CustomPropertyValue is 1..n (actually n=count(CustomProperty) :) )

I think you see where I'm going and might already sound wrong because it's not normalized (or I think it's not because instead of saving the properties as columns i save them as row). Anyway, the problem needs to be solved.

One approach would be to save the value of the property as a varchar. The problem is that I "loose" the types of the property which might be a problem if i want to perform comparisons in my searches (imagine that i save his birthday as a varchar - i can't order them in my query).

Another approach is to have several tables like: CustomIntValues, CustomIntStrings, CustomDates etc and disperse the properties in these tables. The problem is, I think, that if you have many tables, retrieving a lot of properties can be quite a performance hit.

Now, both methods work to some degree and with some hacks. I've seen both of them implemented in several systems, some not that small. Which do you think it's better to use (maybe none)? What improvements can be made? Do you know/can think of any better method?

I've studied Hibernate a bit more (the "Java Persistence with Hibernate" is very good) and i think i can do the following:

- Make CustomPropertyValue abstract
- Create the IntCustomPropertyValue, StringCustomPropertyValue etc as childs of CustiomPropertyValue
- Map everything using the "table per class hierarchy" strategy

this might work quite well. i have to give it a try. I think at this point i don't even need the CustromProperty class. I only need the value ones.

what do you think?

I can suggest to use one string field for this and format each data type so it will be sortable. For examle int 1 will be 0000001 or longer if you want to store Long data type as well. Date will be 2008.05.24 11:48:00. In your class you can create accessors to do automatic conversion for each value. For example setProperty(int value), setProperty(Date value) and so on. You can store a datatype in separate column, so if you call accessor Object getProperty() the correct datatype will be returned.

Hmm, yes this is an idea. Do you think that if I create some special mapping types this might help me? With some work i could make this whole process transparent to upper levels. But I'm worried about data integrity if I append and cut 0's and other stuff all the time. Plus, to make sure cache works rights, i would need to implement very well the .equals method to make sure the int 1 and the string 0000001 are seen equal.

Yes, you will have to implement equals which takes into the considiration the datatype of the property.