With a few PIM systems, I had a look under the hood exactly because of this.
They allow dynamic model changes during run time and you do not really need to restart.
The way I have seen this being implemented is you have a meta model above the actual database tables.
So, the fields the users can add to the application are not really new fields in a table or new tables altogether, but already adding to the existing model.
365体育足球滚球比分So, the way I have seen this implemented is that you have tables for
- item types
- attribute types
In your context, item types would be things like leads, opportunities, customers, proposals, contracts and so on.
Items would be individual records of each time.
Attribute types would describe the basic data type of the attribute (boiling down to the database types).
Attributes are actual values of an attribute type for an item of an item type.
365体育足球滚球比分As context: In the traditional world, you would have a table for each item type
and each attribute of either would be a defined column and each row would be an entry.
So imagine, you need to add, I dont know, twitter handle, to the customers.
What do you do?
You extend your customer table to include an additional row for twitter handle. This means you need to change all the code where customer is being used as you have an additional attribute.
365体育足球滚球比分In the meta-model world, for the same example (adding twitter handle to customer) you would need to do this:
you define a new attribute "twitter handle" of attribute type text (which might just be handled as varchar(200) - it depends on how deep you want to go here).
This gets added to item types table, as separate line linking the item type "customer" to the attribute "twitter handle" (basically two IDs only).
That is the model where there is a definition that you are allowed to have twitter handles for customers.
365体育足球滚球比分Now in the "items" table, you for each customer entry, you add a new line for each existing customer ID and the attribute ID of "twitter handle" which has yet another ID and a value field which contains the actual value.
I have simplified this a lot to make an example out of it. You need to think of revisions, relations, complex data types and so on, as well.
Bottom line is: you only rarely need to really alter tables, but use additional rows in existing tables for definitions of additional data fields.
One of the downsides of this is that you cannot really use caching mechanisms, as you have very generic SQL statements over narrow tables with millions of entries.
One company uses a mixed model where they kept certain attributes in actual tables for performance reasons. Upside: very fast. Downside: extending this meant a reboot of the application and an extension of the application logic.
Granted, this was not hard (xml files needed to be edited), but you need to plan such extensions.
Another company uses ElasticSearch to contain certain fields for quick search access where the index gets updated with each change of a record and only goes back to the database for more complex queries. Very fast. Downside: DB and ElasticSearch can run out of sync. Again, not a big thing to fix, but you need application downtime then.
365体育足球滚球比分I hope this answers your question?