I want to make an electronic journal for maintaining a list of equipment, with the ability to create parameters in the client part, i.e. A new position appeared with its own unique set of characteristics, the user added equipment, created the necessary parameters and added them.

For these purposes, I made the following tables in the database:

Handbook of equipment parameters

CREATE TABLE dbo.ParametersOfEquipments ( Id INT IDENTITY, TypeId INT NOT NULL, Name NVARCHAR(50) COLLATE Cyrillic_General_CI_AS NOT NULL, Created DATETIME NOT NULL, AuthorId INT NOT NULL, Deleted TINYINT NOT NULL DEFAULT (0), CONSTRAINT [PK_dbo.ParametersOfEquipments] PRIMARY KEY CLUSTERED (Id), CONSTRAINT [FK_dbo.ParametersOfEquipments_dbo.Users_AuthorId] FOREIGN KEY (AuthorId) REFERENCES dbo.Users (Id) ) ON [PRIMARY] GO

Table equipment

 CREATE TABLE dbo.Equipments ( Id INT IDENTITY, TypeId INT NOT NULL, Name NVARCHAR(MAX) COLLATE Cyrillic_General_CI_AS NULL, Model NVARCHAR(MAX) COLLATE Cyrillic_General_CI_AS NULL, AssetNumber NVARCHAR(15) COLLATE Cyrillic_General_CI_AS NOT NULL, UnitId INT NOT NULL, Created DATETIME NOT NULL, AuthorId INT NOT NULL, Deleted TINYINT NOT NULL DEFAULT (0), CONSTRAINT [PK_dbo.Equipments] PRIMARY KEY CLUSTERED (Id), CONSTRAINT [FK_dbo.Equipments_dbo.EquipmentTypes_TypeId] FOREIGN KEY (TypeId) REFERENCES dbo.EquipmentTypes (Id) ON DELETE CASCADE, CONSTRAINT [FK_dbo.Equipments_dbo.Units_UnitId] FOREIGN KEY (UnitId) REFERENCES dbo.Units (Id) ON DELETE CASCADE, CONSTRAINT [FK_dbo.Equipments_dbo.Users_AuthorId] FOREIGN KEY (AuthorId) REFERENCES dbo.Users (Id) ON DELETE CASCADE ) ON [PRIMARY] GO

History of equipment parameters

 CREATE TABLE dbo.HistoryParametersOfEquipments ( Id INT IDENTITY, EquipmentId INT NOT NULL, ParameterId INT NOT NULL, Value NVARCHAR(MAX) COLLATE Cyrillic_General_CI_AS NULL, ValidFrom DATETIME NOT NULL, ValidTo DATETIME NOT NULL, Created DATETIME NOT NULL, AuthorId INT NOT NULL, CONSTRAINT [PK_dbo.HistoryParametersOfEquipments] PRIMARY KEY CLUSTERED (Id), CONSTRAINT [FK_dbo.HistoryParametersOfEquipments_dbo.Equipments_EquipmentId] FOREIGN KEY (EquipmentId) REFERENCES dbo.Equipments (Id) ON DELETE CASCADE, CONSTRAINT [FK_dbo.HistoryParametersOfEquipments_dbo.ParametersOfEquipments_ParameterId] FOREIGN KEY (ParameterId) REFERENCES dbo.ParametersOfEquipments (Id) ON DELETE CASCADE, CONSTRAINT [FK_dbo.HistoryParametersOfEquipments_dbo.Users_AuthorId] FOREIGN KEY (AuthorId) REFERENCES dbo.Users (Id) ) ON [PRIMARY] GO

In the ParametersOfEquipments table there is a TypeId field TypeId suppose to create a directory of parameter types, i.e. something like: string , int , DateTime , bool .

But some parameters should be selected from the directory: for example, the Unit parameter ( UnitId field in Equipments ), or the Equipment state should be selected from the directory of possible states, etc.

Please tell me how it is necessary to change / modify the above scheme to implement the selection of the values ​​of some parameters from the tables of directories in the database.

    2 answers 2

    The scheme that you use for flexible setting of equipment parameters is called Entity-Attribute-Value ( EAV ) - entity-attribute-value . Also known as an object model or vertical model .

    In the article on en.wiki, the following link highly recommends reading the following points for reading:

    • 4.3 The Value
    • 8 Scenarios that are appropriate for EAV modeling
    • 9 Working with EAV data
    • 12 Alternative XML

    In short, then.

    Storing the values ​​of various types of parameters, as you have done, in a single column of type Value NVARCHAR(MAX) is the easiest way, but also the most inefficient. It is better to make a column for each type (in this case, you can make them SPARSE ):

     CREATE TABLE dbo.HistoryParametersOfEquipments ( ... ParameterId INT NOT NULL, ValueString NVARCHAR(MAX) SPARSE NULL, ValueInt INT SPARSE NULL, ValueBool BIT SPARSE NULL, ValueDateTime DATETIME SPARSE NULL, ... )

    For large data volumes, values ​​of different types are spread over the tables — its own table for the values ​​of each type of parameter.

    If the parameter values ​​can refer to directories, this can be done by adding the ValueLookupID INT column to the value table that refers to the corresponding directory. If there are several directories, then they either add a column to each of them, or combine all the necessary directories into one, dividing the values ​​in the general directory by groups.

    It is also worth considering alternatives, such as storing parameters and values ​​in XML type columns (for example, if the parameter sets for different pieces of equipment are extremely heterogeneous).

    And last but not least. Because EAV is not displayed in the relational model without flaws, it is extremely important not to be mistaken that it is EAV that should be used to solve a specific task, otherwise it may be inconvenient to work with it.

    • in fact, the main set of parameters will be as follows: Division, State , of the optional I assume: Release date, Serial number, but other parameters are possible, for example: Date of previous / next THEN well, etc. - Bald
    • @Bald, I can hardly understand how it will be used better than you, so I can only give general recommendations. The crucial point is whether users will create parameters (this inclines in favor of EAV), or rather, the parameters can be defined in advance (even if they are a couple of dozen and some of them are optional), and users will only ask them (this inclines in favor of the relational model, i.e., passing parameters to the columns). - i-one
    • @Bald, I also note that you now have ValidFrom, ValidTo, Created, AuthorId can be specified for each parameter. In the relational model, when making parameters in columns, keep 4 add. for each column it will be inconvenient, it will be more convenient to have ValidFrom, ValidTo, Created, AuthorId for the entire set of parameters at once. If this is unacceptable, then it declines in favor of EAV. Combined (EAV + relational) approaches also apply. Sometimes (if, for example, there are no reports / requests under the additional parameters), it is enough to have a text field for comments, where the user will specify the required add. intelligence. - i-one
    • Attributes can be changed individually, so for each parameter there is a period of validity, in the future I plan to use equipment parameters for building various samples: for example, to get a list of equipment that should have a period, etc. I haven’t decided how to deal with the values ​​of parameters from the reference book, currently my reference books are separated according to different tables - Bald
    • @Bald, then apparently all the same EAV. As for directories, I would probably do that. If there are not too many of them used in the values ​​of the parameters (say, a couple or three), then I would create a column in the reference book. If there are dozens of them, then I would combine the directories into one. - i-one

    Add another data type "enum". Create a table of the form:

     create table enums( Id INT NOT NULL, ParameterId INT NOT NULL, Name NVARCHAR(XX) NOT NULL, CONSTRAINT [PK_enums] PRIMARY KEY CLUSTERED (Id), CONSTRAINT [FK_enums_parameters] FOREIGN KEY (ParameterId) REFERENCES dbo.ParametersOfEquipments(Id) );

    In enums enter possible values ​​for these types of parameters. In the HistoryParametersOfEquipments table HistoryParametersOfEquipments in the Value field, put the ID from this table, when the specific value of the parameter should be written to the specific equipment. Referential integrity will have to be controlled by triggers. Alternatively, decide that the different types of parameters in the HistoryParametersOfEquipments lie in different fields and then you create a ValueEnum field from which you can make an honest foreign on the ID from enums.

    But such a scheme is convenient when all the directories in the system are in the same table with a single cross-cutting ID. Possible with extension tables for specific parameters. If the directories already exist and each has its own ID in its table, then it is possible to enums primary key (id, ParameterId) in enums and duplicate the ID and names with the triggers from the main reference table. For each directory in the system, immediately create an entry in ParametersOfEquipments that later everyone uses it.