Why simple collections aren't mapped as a CSV?

icetbr · **Joined:** Fri Oct 06, 2006 5:02 pm **Posts:** 11

I know there are ways to achieve this, but they're far from common, it's something I don't ussually see. I wonder what are the advanbages of the extra table approach.

As an example, consider this:

Code:

public class Car {
   private String model;
   private String year;
   private Lit<String> availableColors;
}

"availableColors" can have from 1 to 3 Strings that represent a color.

In my database I'll have 2 tables

Code:

       CAR                  CAR_AVAILABLECOLORS 
id  model     year                car_id          availableColor
1   awsome  2008                 1                     red
                                           1                    blue

Why can't I have one table with an extra column "availableColors" with a comma separated list of values?

Code:

                      CAR        
id  model      year   availableColors 
1   awsome  2008   red,blue

nordborg · **Posted:** Wed Aug 06, 2008 2:15 am

I don't think this a generic functionality that needs to be in Hibernate. I can think of several problems:

1. What if the possible values themselves contains a comma?
2. How do you find all Cars that are available in red color with a query?
3. If the values are numbers you may want to do calculations on them. Eg. the sum or average.

If you still want this, you can probably do this by implementing a custom UserType. See http://www.hibernate.org/hib_docs/v3/re ... pes-custom

schauder · **Joined:** Thu Jul 05, 2007 9:38 am **Posts:** 287

Another problem would be the size. Collections get big fairly easy and there is no way to determine how big the field needs to be.

So Hibernate would be forced to make rather strong limitiations, or use (e.g. in Oracle) BLOBs or something to that effect which can't even used with like

icetbr · **Joined:** Fri Oct 06, 2006 5:02 pm **Posts:** 11

Ok, I see your arguments.

Quote:

1. What if the possible values themselves contains a comma?

- Then use white space, or any other user defined value

Quote:

2. How do you find all Cars that are available in red color with a query?

- Perhaps the major problem. For one, it could be not a very common query. A "like" will do just fine ("like" is slower than equals)

Quote:

3. If the values are numbers you may want to do calculations on them. Eg. the sum or average.

- Your aplication can do that for you. I'm not saying you should always use this approach, just that it would be nice to have the option.

Quote:

Another problem would be the size. Collections get big fairly easy and there is no way to determine how big the field needs to be.

- It could be a user defined size, although I am talking about a really small fixed set of values (1 to 3 strings of size 5 to 12 each)

Quote:

If you still want this, you can probably do this by implementing a custom UserType.

Sort of overkill to have just the string representation of a list ([one,two,three]) stored, but that would be the right way to do it (I could do some hacks in my code to trick hibernate, but that would be ugly)

It seems to me that it just isn't that common, although I do kind of use a lot of this. I just don't like to see my database filled with lots of tables that aren't entities. I do browse my database a lot, and it is much more user readable to see a csv list.

Jut as an example, Wordpress use this "comma (or other character) separated list" of values I'm talking about.