I have run some tests on our production data and the results look promising. I want to convert all my dropdowns to actual MySQL enum type from the current VarChar types. I am rewriting the MySQL code in Sugar to support this so my QRRs and any New Dropdowns are created as mysql enum type instead of Varchars if I choose to do so..
There seems to be no other code changes needed. All the queries, reports, widgets and logic hooks all work normally.
So the real question......
Does anyone know of any pitfalls that I am missing here? In my tests (and we have A LOT of drop downs) the DB used much less memory and was a lot faster using the enum type over a varchar, sorting was the only pitfall I have seen yet but it was easily overcome. Of course it shrank my databases by gigabytes as there is much less data actually stored in the tables. I understand updates to the list will be very expensive but we only do that during maintenance windows anyway and there are ways around that if it gets too bad.
- To facilitate upgrades and have written a script to convert the fields back and forth. To enums for use and back to varchars for upgrades, at least until SugarCRM supports it if ever.
- I don't bother with Stock fields, just custom ones. Since both types of fields are code agnostic I can use them both side by side without any code changes to anything.
- I have a checkbox on the DropDown field definition screen that allows you to use the legacy (VarChar) format for any enum that you know will change a lot (like I have a field that chooses the trade show they are at and we add trade shows all the time, so that one I would like to remain a Varchar.
- I had to add logic to the List Editor so if you eliminate an item it pops up a 'remap' option to maintain data integrity.