I want to develop a generic CRM application using SQL Server 2008.
The application must enable custom user fields using some extension technique.
I read a lot about the different options such as:
But unfortunately these options are difficult to implement and I understand the application performance is affected and also it's difficult to query the repository using Entity Framework Code First.
I would appreciate some other simple options to implement.
Most of the time a package extension system like this is used to add user-defined columns to existing tables.
You can allow users to define their own tables which are placed in their own (segregated) schema. Your administration UI which manages this extension process will need to run the DDL code necessary to build these user-defined tables. If they are defined as being an extension of a predefined table, your system should enforce a 1:1 relationship between the predefined and user-defined table that it extends. You could also allow free-standing user-defined tables, if you want.
Once you have your user-defined tables created, use a (predefined) mapping table to connect the user-defined tables/columns to your predefined fields in your CRM UI. If you also allow users to define UI forms and fields, then your mapping table can tie to these too/instead.