Choosing a Design for a Customizable Data Structure?

Question

I plan to create an application that should allow the user to customize their own data model (i.e. create fields, data structure, etc.) dynamically.

I have several technical capabilities, all have flaws.

• in the admin screens, update the SQL schema in the database to reflect the changes.
  • I am afraid that this is a very bad idea because of the rights that the application should have in the database. Moreover, if every click should be applied to a new sql schema, I believe that I will work directly on the hole. This is the approach I've seen in most user-customizable applications.
• create a set of additional additional columns in the database schema and hope that there are enough columns for complex data models.
  • this can quickly be a functional limitation if I cannot resolve more than X columns in my application
• save in one table all the elements with an ID column and an Xml column to store custom columns.
  • this approach can remove the aforementioned inferences because the sql schema will remain static, but since the EF (which I was hoping to use) does not know how to control the xml data type, I will have to either manually SqlCommand with an XML function, or write a custom EF provider that, I think it will be quite a lot of work.
  • This approach, chosen by Microsoft for SharePoint ... it allows me to think that this is the best (or at least less bad)
• create a property table with a main column itemId, a column for the property name and a column for the property value
  • this approach involves a very large table (properties of X elements * Y for an element)
  • I will need to store my values ​​in plain text, even if it's a numeric number.

My requirements:

• keep the code supported, single tested and all fashionable technique.
• have a responsive application with a lot of data
• have the most reliable application possible
• allow users to fully customize their application (create your own view with a filter / sort by user properties).

I believe that choosing the right design should be good, because it would be very difficult to change the latter.

Any feedback would be appreciated.

Answer 1

One option is to use a NoSQL database such as MongoDB, which does not have a schema. New fields do not have to be defined in front (without the headaches of modifying the circuit), and different records may have different fields. This is one of the benefits of a NoSQL repository like this.

eg. in mongo, your “table” could have these 2 records inside in legimitely:

{ "ID" : 1, "FirstName" : "Joe", "LastName" : "Bloggs", "FavouriteColour" : "Blue" } { "ID" : 2, "FirstName" : "John", "LastName" : "Smith", "DOB" : "2000-01-01" } 

Adding to a new field is as easy as starting to include it in your entries.

In my experience, having a fully flexible / dynamic schema in an RDBMS such as SQL Server can be a little painful and difficult to achieve high performance. I had experience with options 1) and 3) that you indicated. When the data was saved as XML, in the end I usually had to grind the data into a relational form for certain purposes.

Answer 2

I must say that anyone who has full performance is not 100% realistic.

Assuming you are using a relational database, I would go with option # 1. You still have the option of using the storage design that the RDBMS quickly does. You can reduce the security risk by using stored procedures to make changes to the DDL and restrict the execution rights of these SPs.

Option 2 can be performed, but when trying to find out if there is a widget color in UDFText39 or UDFText52, there may be problems with maintenance.

The "large amount of data" seems to exclude option 3 if you are not coming with a non-relational solution. In a DBMS, this will be rather slow.

Option number 4 is a bad idea, because you are forced to not only mix data domains (colors, sizes, etc.), but also data types. Stay away from this.

Answer 3

I would say that the cleanest solution would be # 4.

Create a table for each data type that you want to use. - numeric - string value - datetime -... value

so that you do not have an incredible huge table, and your strengths are typed at the same time. only limitation: you are limited by the number of supported data types. but this IMHO is a natural limitation.

Answer 4

I do not think that you can use EF or another ORM structure without changes. You will need special code, but we like to create new things, right?

I see two not very bad solutions:

1) Like your 1. solution, use 2 tables, one with the definition of the columns and the second for the data. For example, a definition table might look like this:

  Name Type Description
  MyColumn1 int int column
  MyColumn2 string string column

The data table contains common columns with names filled with actual data.

  Col1 col2
 1 string 1
 2 string 2

When you request your "data type", you read the "definition" and then request the actual data. You can store additional attributes in the definition table, for example, validators ...

3) If you use MS SQL> = 2008, the third solution looks like a good one. I still recommend a separate table for each data type.

I do not recommend solution 2, it seems like a bad hack. Solution 4. looks clean, but this approach is not suitable for large data sets.

Answer 5

It seems like this would be a good candidate for an EAV template (entity attribute value) that looks like one of the options you described.

Entity Value attribute template .

For further reading at a slightly more understandable level, you can read this article> Attribute-value-system .