ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Clinton Begin" <clinton.be...@gmail.com>
Subject Re: iBatis and entity-attribute-value style tables
Date Sat, 03 Mar 2007 04:49:19 GMT
Hi Collin,

iBATIS can absolutely query this...whether or not you'll like the structure
of the object at the end of the day is a different story.  :-)

There are two possibilities here:

1) You can simply create a model object that represents the table structure
and build the API around it to access the properties.  A sort of
JavaBean-ish class backed by a list of properties..

2) You can use queryForMap.  This will return a map that has a key for each
property with the value containing the value from the table.  The
disadvantage to this is that there's no way to map it directly in the SQL
Map XML file, but instead you would have to do it for each object in your
result set.  Hence you could find yourself in an N+1 selects problem.

In all cases it's a performance and complexity nightmare.  All of the
limitations are at the SQL level.  iBATIS can't perform magic, even though
it can ultimately map it.

My recommendation is to take a more hybrid approach.  Put AS MANY properties
directly on the user table as you possibly can.  Be as gratuitous as you can
-- worry less about null or default values and more about simplicity.  Then
leave the dynamic properties for the flexibility.  This will allow you to
map most of your properties normally and realize the performance and
simplicity benefits of a properly normalized model.   Perhaps refactor your
database often to move frequently used dynamic properties to the real table
model and remove any less popular ones into the dynamic table.

Cheers,
Clinton

On 2/27/07, Collin Peters <cadiolis@gmail.com> wrote:
>
> Does iBatis have any kind of built-in support for
> entity-attribute-value (EAV) style tables?  An EAV table (or key-value
> table) is one where the data is represented in rows instead of in
> columns.   A simple example is here:
> http://www.devshed.com/c/a/MySQL/Database-Design-Using-KeyValue-Tables/
>
> So for example, if you had a users table such as:
> USERS
> ----------
> user_id  integer,
> firstname text,
> lastname text
>
> And you needed to represent properties for each users, you could add a
> few new columns:
> property1  text,
> property2 text,
> etc...
>
> But the problem is that you are locked down by the columns. i.e. for
> each property you add, you need a to add a column to represent that
> property.  EAV would re-structure this by having another table called
> user_properties which would look like:
> user_property_id integer,
> user_id integer,
> property_name text,
> property_value text,
>
> So you gain flexibility at the cost of complexity.  We have a
> situation like this and I am wondering if iBatis can do some kind of
> magic to convert the properties automatically.  Let me know if you
> need a more concrete example to understand this.  Before I spend the
> time to do that I will see if anyone has any comments.
>
> Regards,
> Collin Peters
>

Mime
View raw message