All geek questions in one place

Custom Fields in .Net and SQL Server

We have a requirement for our project for custom fields. We have standard fields in the table, and each client wants to be able to add their own fields. At the moment, I am not interested in how this will work in the user interface, but I want to know what options are intended for backing up and restoring data on the rear panel. The last time I did something similar, it was about 10 years ago in VB6, so I would be interested to know what options for this problem .Net world today.

The project uses a SQL server for the backend, linq-to-sql for ORM and the front end of C # asp.net.

What are my options for this?

thanks

+6
source share
3 answers

There are four main options:

  • actually change the schema (DDL) at runtime, however, ORM will not like it to a large extent and, as a rule, has security problems, since your “application” account usually should not redefine the database; however, he avoids the “internal platform” effect of the following two
  • use the key value storage in the form of rows, that is, the Customer table can have a CustomerValues table with pairs, such as "dfeeNumber" = 12345 (one row for each custom key / value pair), but the pain is to work with (instead of "get", this "get" and "list" for each object)
  • use a single piece of data (xml, json, etc.) in one CustomFields cell - again, not ideal for work, but easier to store atomically with the main record (drawback: forces you to load all user fields for reading one)
  • use the document database (no schema at all), but then: no ORM

I used all 4 at different points. All 4 can work. YMMV.

+11
source

I have a similar situation in the project I'm working on now.

  • Forget linq-to-sql when you have a flexible database schema. It is not possible to update linq-to-sql models on the fly when the database schema changes.

  • Solutions:

    • Save the additional table with the name of the table to which the values ​​belong, column name, value, etc.
    • Fully dynamically change the table layout every time they add a field.
    • Use a NOSQL solution such as mongoDB or Azure table storage. The NOSQL solution does not require a schema and can be changed on the fly.

This is a handy link 2:

http://asktom.oracle.com/pls/asktom/f?p=100:11:59::::P11_QUESTION_ID:10678084117056

+1
source

You mean the EAV model (entity-attribute-value).

Here is the article: http://hanssens.org/post/Generic-Entity-Attribute-Value-Model-e28093-A-POCO-Implementation.aspx

0
source

Source: https://habr.com/ru/post/896948/

More articles:


All Articles