Hibernate version: 2.1.6 & 3.0rc1
Name and version of the database you are using: mssql & mysql
I'm about to add functionality to an application where the system administrator should be able to add properties/fields to objects from the GUI without actually alter any physical tables. Adding a property is just adding a metadata record where you specify name, data type, length, etc.
And then tell for which object the property should be available/visible.
But I want to know that my solution gives good query performance. The number of property records could be large 1.000.000+
I'm thinking something like this. The metadata I'm pretty confident with.
Code:
create table company (id long primary key ...);
create table contact (id long primary key ...);
create table project (id long primary key ...);
create table propertymetadata (id long primary key, name varchar(20) not null, type int not null ...);
);
But when it comes to the the actually property value itself, I'm not so sure.
Alternative 1 (value is a string).
pros=single column, cons=cannot perform efficient queries if numbers or dates are stored as string):
Code:
create table property (id long primary key, property long not null, owner long not null, value varchar(255));
Alternative 2 (one column for each data type).
pros=efficient queries for each datatype. cons=only one column is used per record.
Code:
create table property (id long primary key, property long not null, owner long not null, stringvalue varchar(255), datevalue datetime, intvalue int);
Alternative 3 (one table for each data type).
pros=efficient queries for each datatype. cons=many tables.
Code:
create table stringproperty (id long primary key, property long not null, owner long not null, value varchar(255));
create table dateproperty (id long primary key, property long not null, owner long not null, value datetime);
create table intproperty (id long primary key, property long not null, owner long not null, value int);
Then comes the next question. Do I have to duplicate the tables for each object that can be owner? i.e. companystringproperty, contactstringproperty, projectstringproperty, ... or can I map a table to be zero-many from multiple tables without having multiple foreign keys (the owner column in my example)? Can I have a single owner column or do I need three "owner" columns (company, contact, project)?
And everytime I have to think about query performance. Storing all property values for companies, contacts and projects in one single table can create huge number of records. Can partitioning be used to spread property values over multiple tables? I know this can be done on the database level. Can it be done in Hibernate 3 mapping?
Does all this make sense or is it completely confusing?
Are there better solutions to solve the "add dynamic properties problem"?
Hibernate 3 upgrade is planned for sometime in June but I'm happy to upgrade tomorrow if Hibernate 3 can help me here.
Regards
/Goran
Login
FAQ
Search
