Getting around the 25 field limit in dotCMS

This is relevant to you if you have ever seen the error message: There aren't any more Data Types available for the one selected, please choose another one and click on save again. For an example see this screenshot.

Out of the box, dotCMS limits Structures to having no more than 25 fields of each data type in the database - and there are six data types. If you have a look at the CONTENTLET table, you will see clearly that it has 25 date, text, text_area, integer, float and boolean columns (labelled date1 .. date25, text1 .. text25 etc). In my current project, I was in trouble because it had quite a lot more than 25 fields in the spec that looked like they should be stored as text fields. This is how I dealt with this issue - without having to increase the limit.

Some terminology first. Display Type defines how the control should be output to the data entry page (when a user adds new content). For example, a control might appear on the page as a checkbox, a text box, a text area or a date picker made out of select controls. As you can see in the screen shot below (click on it to see a larger version), there are 14 different display types.

Each field (column) will be stored in the database as one of the six set Data Types: date, text, text_area, integer, float and boolean. Some display types give you no choice as to what data type can be used. For example, a checkbox display type can only be stored as a boolean column in the database. Others give you some choice. For example, a text display type can be stored as a text, decimal (float) or whole number (integer) field in the database (as you see in the screen shot below - click on it to see a larger version).

This means that there are quite a few ways to represent data on the (html) page and the database. As I mentioned, it looked like I needed way more than 25 text fields in the database, but I dealt with it by keeping the following rules in mind.

• Multi Select and Category display types will always be stored as text data types in the database. When you count up the number of text fields you need to deal with, include multi-selects and category fields in that count.
• When making a field of Text display type, ask yourself if the contents of the field could ever stretch to longer than six words or one sentence. If so, the client will most likely forgive you for making the field a Textarea display type instead, thus removing each such field from the count of text fields.
• WYSIWYG, Tag and Textarea display type fields are stored as text_area data types in the database (not text), so these will never count towards the 25 text field limit.
• File and Image display type fields are stored as integer data types in the database, so these will never count towards the 25 text field limit.
• Most importantly, you can decide whether Radio, Select and Text display type fields shall be stored as text, decimal (float) or whole number (integer) fields in the database.
• If you need to store, age, year, count etc, store these as a decimal (float) or whole number (integer) field so that the user has to type out a number rather than describe the value (36, 1974, 3234 instead of "old!", "last century", "heaps!").
• For select or radio display type fields you need to enter label|value pairs for each option that can be chosen (code). For example, the following code would need a text data type:

  Australian Capital Territory|ACT
  New South Wales|NSW
  Northern Territory|NT
  Queensland|QLD
  South Australia|SA
  Tasmania|TAS
  Victoria|VIC
  Western Australia|WA

  It would need a text data type because the values are alhpanumeric ("ACT", "NSW" etc). This is handy because if the user selects "Victoria", the field value stored will be "VIC" - as a text data type. But if you are running out of text fields, you can use numeric values instead so the field will be stored as an integer data type. For example:

  Australian Capital Territory|0
  New South Wales|1
  Northern Territory|2
  Queensland|3
  South Australia|4
  Tasmania|5
  Victoria|6
  Western Australia|7

  As a rule of thumb, this is a bad practice if the values are not really numeric because:
  • Now your code has to interpret 0-6 every time you need to show/use the value.
  • Any time you need to change this data (modifying, adding or removing an option) you need to make the change in multiple places: in the field definition within the Structure, and every place in your code where you need to show/use the value.
  Having said all that, it is still an option if you are running out of text field columns.
  

In conclusion: not all fields that use a text box need to be stored as a text field in the database; some text fields can be made into text areas and; select/radio controls can be stored as numeric values. Some of these decisions are logical and make sense for the application. Others - like replacing textual values in select/radio controls with numeric values - will most likely be hacks that you should use as a last resort.

OK, but I still need more than 25 fields of a given data type. What do I do? One option is to split the data structure up into two dotCMS Structures and relate instances of each. This is a lot of overhead and will significantly increase both the complexity of your code and the time it takes to enter data. Another, far more experimental option would be to increase the limit. Unfortunately, it seems that dotCMS haven't seriously considered this situation yet, as you can see in the dotCMS Yahoo post There aren't any more Data Types available. The 25 field limit is defined in a properties file (where?), so ultimately it could be just a matter of modifying this value and adding a new column to the CONTENTLET table. But who knows what knock on effects that will have - on database performance if nothing else.