Hi!
After several years of dealing with Sugar/Suite I faced with requirements to organize document-oriented storage. I’ve never had such requirements before.
Here’s the point. I need to keep huge amount of documents for an Account. “Huge” means not only dozens in quantity, but dozens in types. The large part of these documents is of unique type. Moreover data and fields from each document should be available in future for analysis and/or answering questions like “Did this account has an allowance for such kind of activity a year ago?” or … “Is it possible to provide a loan to an Account for 3 years period for food production if its license… Hey, when the license he provided year ago expires?”
To be more specific we have several main types of documents like 1) company foundation documents, 2) financial statements, 3) licenses and permissions 4) agreements, 5) others. I have to not only categorize the documents and have its electronic copy uploaded to CRM but I need to manually process it to insert significant data into database. Some documents have XML copies and we could simplify entering the data for users in the near future. But it doesn’t affect architecture in the whole I guess.
Thus having default Documents module is not enough for us. And I decided that every types of documents has to have its own database table. Or say module with its own SugarBean implementation. But not to build a huge amount of Relate fields and relationships in Accounts module I decided to implement “repository” for each main types which in its turn will hold all the documents of given type. Keeping Accounts module clear from huge amount of relationships is not the only reason I would like to keep each document type in its own class. In this case I could place all the validation and other business logic into this class. Moreover implementing common interfaces by those classes would help me to get answers for questions like “get all valid licenses that account had year ago”.
Here’s my implementation for the idea for founding documents. I created AccountsFoundingDocs module and connect it with ‘one-to-many’ relationship with Accounts module. Next I created a couple of modules say AccountDocTypeA and AccountDocTypeB which I connected to AccountsFoundingDocs again with ‘one-to-many’ relationship. Then I created Flex Relate field in AccountsFoundingDocs that should handle those AccountDocType* modules. I planned to add AccountsFoundingDocs subpanel to Accounts’ DetailView which QuickCreate form would render Flex Relate field that helped me to fill AccountDocType* popup form (remember its document has its own set of attributes!) and upload document’s electronic copy. I managed this while building Shares module for Accounts in which I can add any type of shareholders whether Account or Contacts. And expected the same behaviour for custom modules.
But when I fired up popup for the first time it turned out that there was no beauty Create button to generate new records right in the popup. I jumped into the code and found out that there are “special” AccountFormBase and ContactFormBase classes with huge amount of code. That means it’s almost impossible to re-build all that classes for our dozens of documents. Moreover I have unnecessary search and list views in popup since we have in fact ‘one-to-one’ relationship between every AccountsFoundingDocs and AccountsDocType* entities.
Now I understand that I made up a decision that is impossible within existing SuiteCRM code base. And I need to find a solution.
What ideas I have now after all the experience mentioned above?
I’m thinking of extending default Document class but without defining separate table for module. I.e. keeping Document::table_name property unchanged. Is it possible? In this case I could add any additional ‘source= > ‘non-db’ fields together with business logic needed. But where am I planning to store all that fields? What about serializing all that ‘non-db’ fields into JSON-like structure and save into varchar field with default SugarBean methods or even extend SugarBean with my own methods for working with recently added to MySQL JSON data type fields http://dev.mysql.com/doc/refman/5.7/en/json.html ? In such case I just need two database fields for setting document type and varchar/JSON field for storing actual data. And of course I need a map which points document type to repository type it belongs to.
Did anybody encounter such scenario with document-oriented storage on top of Sugar/SuiteCRM? Have you managed to solve the problem? Do you know any solutions (open source/proprietary/commercial/don’t care) for it?