T-SQL, tell me how best to build a model?
Good afternoon. The problem was: The following basic table (small columns down)
There are different attributes for each operation.
- Purchase of car = Basic Contract Number, cost of car, etc.
- Transformation of motor vehicle = main FIS owner, Auto Number, etc.
The point is, the parameters for almost every operation are different. (Some operations have the same pairs)
In design, a dilemma was encountered as better as to:
- Create one additional table to store all attributes
- Create some tables for each operation.
- It's just that all the attributes are in the main table.
The request will generate Linq, so in the first and third case, all the columns will be removed (There will be ~25 and almost all nvarchar) and in the second, there will be many Join's empty fields.
How should we do in terms of high productivity and flexibility (new operations or attributes)?
This decision depends on how you use the data: what display, filtering, reporting, etc. My recommendation does not take into account the system requirements, so it will be a common practice.
I would recommend option two, but a little modified.
There is one general operating table that will provide common fields for all operations. For example: type of transaction, date of establishment, object(s), name, description, etc. And from there, make reference to the tables for every operation.
From the pros:
- Simplification of the data presentation. Very often, the user, looking at history, is able to see the general transaction data (date, type, etc.) and then to open the details and watch the details. I mean, when you're looking, it's only gonna be in one table.
- Simplifying the task of filtering in common fields. For example, there's no need to check the field of creation in every table, but only in the general.
- Indices will be better operated. But for as long as it depends on specific filtration/check requirements
- Other benefits of option 2
- The code itself will be a little more complicated. Transactions are required for entry/change/deposition, smart field update (if the name is changed, one table, if the field is the other table).
- If a TK operation can change its type, this option is not very convenient, because it has to be copied and deleted, texted, etc.
Z.S., you can deepen, but you need to know more detail about the TK and the nuance of the use of the base on the side of the elevator.