Way too much custom fields needed, will this solution work?

[Franky_Ruyssch2]

Our custom CRM system contains hundreds of fields (attributes) related to companies and / or contacts. The number of fields is an ever-changing thing, meaning one day we remove some, another day we add some.

The overview in the backend is lost, so we need to have another solution for this. We came up with the following, but I am not 100% sure that the ‘solution’ is future proof.

At the base we divide our attributes in attribute groups: e.g. machinery (group 22)

At the second level we define the attribute itself: e.g. printer (attribute 155)

At the third level we define the value e.g. true

Our internal notation would be something like

[attribute-group];;[attribute];;[attribute-value] -> 22;;155;;true

This 1-attribute definition will be part of a far bigger string, values are separated with 2 pipe symbols. ( ‘||’) ( and we use double semicolons to separate group-attribute-value )

The idea is the put the complete attribute string in 1 custom field, something like

12;;111;;ABC||23;;244;;A value||22;;155;;true ….

I performed some tests to see if triggers will work, for example a data values change where I check if the attribute string contains 2 defined values.

This all seems to work but again I am not sure if all things I will need in the future will be possible.

A drawback of this is ofcourse the fact that I will have to ‘translate’ the attribute

 id’s to real values before I will be able to use them on landing pages / emails.
( in the example above : 155 has to become ‘printer’ )

Most probably I will need extra fields in the backend for storing the ‘translated’ content for further usage.

Do you guys see more pros and cons in using this kind of approach?

[SanfordWhiteman · Accepted solution]

Use JSON: that's what it's built for.

There's no reason to attempt your own serialization format when JSON is industry-standard and spoken fluently by both JavaScript and Velocity (as long as you use Velocity-safe JSON).

[Franky_Ruyssch2]

Hi Sanford,

thanks for your reply.

Apart from the fact that json is the better solution, do you see any problems using this way of data storage?

[SanfordWhiteman]

Even if you can ensure no delimiter collision, the persistent problem is it's not self-describing.

No one can look at your format and know what it means, while any modern-day developer has to understand JSON. You also have to write custom parsers (which may not be in your wheelhouse and may have untested error cases) for both JS and Velocity. That won't happen with JSON.

Does this mean you can never create an in-house serialization format that is successful? No, but I wouldn't bet on it.