0

I've seen quite a few software especially CRM tools which allow the user to define their own fields to capture user's data. They also allow the user to define the data type of that field (text, number, date etc.). I am wondering how do they handle this thing at the application and database layer. I could think of following way to do it:

  1. Store all the values as string in the database along with their data type and parse/convert it to the it's intended data type at the application layer for any operations.

The problem I see with this approach is that you can not query the database. All the sorting or filtering of data has to be done at the application layer. Hence, I was wondering that there has to be a better solution.

1

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
  • items
  • attribute types
  • attributes

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

  • customer
  • proposal
  • quote
  • lead

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).
365体育足球滚球比分 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?

| improve this answer | |
  • Great! Can you please elaborate more on "meta model above the actual database". Is it like some standard thing that I am missing out? Would be helpful if you can point me to the right resource to read more about it. Thanks! – Anmol Gupta May 24 at 5:52
  • I dont know of any material to read up on this. However, I had a few discussions over the years about how to do this with architects. If this is of any help to you, I can extend my answer. How about it? – Snorik May 24 at 13:45
  • Sure, that would be helpful! – Anmol Gupta May 24 at 17:34
  • I have extended my answer, please have a look. – Snorik May 24 at 19:02

Not the answer you're looking for? Browse other questions tagged or ask your own question.