Members, good afternoon! Faced the following problem: There is the following main table (lowered columns lowered)

Maintable

{ id_main (PK,Identity) id_operation (FK) }

For each operation there is a different number of attributes.
For example:

  1. Buying a car => Contract number, car cost, etc.
  2. Renewal of the car => name of the owner, car number, etc.

The bottom line is that the parameters for almost every operation are different. (Some operations have the same par-ry)

When designing, I faced the dilemma of how best to do it:

  1. Create one additional table in which to store all attributes
  2. Create multiple tables for each operation.
  3. Simply all attributes to hammer in the main table.

The request will generate Linq, so in the first and third cases absolutely all columns will be unloaded (There will be ~ 25 and almost all nvarchar), and in the second there will be many Join'ov over empty fields.

What is the best way to do in terms of high performance and flexibility (new operations or attributes will be added)?

  • one
    maybe look in the direction of xml or json columns, i.e. there will be one table with main columns and an additional column in which will be the xml / json of the specific schema. - Alexey Shmelyov
  • What is your understanding of linq? There are such libraries as linq2sql and entity framework, both use linq syntax to build queries to the database. - Pavel Mayorov

1 answer 1

Such a decision depends on how you will use the data: what are the requirements for the display, filtering, reports, etc. My recommendation does not take into account the system requirements, so it will be from general practice.

I would recommend option two, but a bit modified.

One common table "Operations" is created, in which there will be common fields for all operations. For example: type of operation, date of creation, object (auto), name, description, etc. And from there to make links to tables for each operation.

Of the benefits:

  • Simplify the task of displaying data. Very often, the user, viewing the history, it is enough to see the general data about the operation (date, type, etc.), and then open the specific and see the details. Those. when displaying it will be necessary to search only in one table
  • Simplify the task of filtering by common fields. For example, you will not need to check the "creation date" field in each table, but only in the general one.
  • Indexes will work more optimally. But by how much - depends on the specific requirements for filtering / searching
  • Other advantages of option 2

Of the minuses:

  • The code itself will be a little more complicated. We need transactions when entering / changing / deleting, smart update of fields (if we change the name - one table, if the special field is another table).
  • If according to the TZ the operation can change its type, then this option is not very convenient, since it is necessary to copy and delete records, rewrite links, etc.

ZY you can go deeper in more detail, but you need to know more details of the TK and the nuances of using the base from the software side.

  • If the details of the operation are not involved in the search, I would suggest to simplify: the general table of operations + the rest of the data in the same table. Create a Details field of type nvarchar. There we put specific data of operations in the form serialized in JSON. Then they can have any structure, since Serialization / deserialization operations will occur only on the client (in .NET). The main disadvantage is that these fields are only for data storage. Search and indexing on them is problematic. - nikita