Best practice for verifying correctness of data in MS SQL

We have multiple tables with different data (for example masses, heights, widths, …) that needs to be verified by employees. To keep track of already verified data, we are thinking about designing a following table:

TableName varchar
ColumnName varchar
ItemID varchar
VerifiedBy varchar
VerificationDate date

This table links the different product id’s, tables and columns that will be verified, for example:

Table dbo.Chairs
Column dbo.Chairs.Mass
ItemId 203
VerifiedBy xy
VerificationDate 10.09.2020

While creating foreign keys, we were able to link the ItemID to the central ProductsID-Table. We wanted to create two more foreign keys for database tables and columns. We were unable to do this, since “sys.tables” and “INFORMATION_SCHEMA.COLUMNS” are views.

How can I create the foreign keys to the availible database tables/columns? Is there better way how to do such a data verification?

Thanks.

Answer

You can add a CHECK constraint to verify that the correctness of the data which is inserted/updated in the columns TableName and ColumnName, like this:

CREATE TABLE Products (
    ItemID VARCHAR(10) PRIMARY KEY,
    ItemName NVARCHAR(50) UNIQUE
)

CREATE TABLE Chairs (
    ItemID VARCHAR(10) PRIMARY KEY,
    FOREIGN KEY (ItemID) REFERENCES dbo.Products,
    Legs TINYINT NOT NULL
)

CREATE TABLE Sofas (
    ItemID VARCHAR(10) PRIMARY KEY,
    FOREIGN KEY (ItemID) REFERENCES dbo.Products,
    Extendable BIT NOT NULL
)

CREATE TABLE Verifications (
    TableName sysname NOT NULL,
    ColumnName sysname NOT NULL,
    ItemID VARCHAR(10) REFERENCES dbo.Products,
    VerifiedBy varchar(30) NOT NULL,
    VerificationDate date NOT NULL,
    CHECK (COLUMNPROPERTY(OBJECT_ID(TableName),ColumnName,'ColumnId') IS NOT NULL)
)

You need to grant VIEW DEFINITION on the tables to the users which have rights to insert/update the data.

This will not entirely prevent wrong data, because the check constraints will not be verified when you drop a table or a column.

However, I don’t think this is necessarily a good idea. A better (and more conventional) way would be to add the VerifiedBy and VerificationDate to the Products table (if you can force the user to verify all the properties at once) or create separate columns regarding each verified column (for example LegsVerifiedBy and LegsVerificationDate in the Chairs table, ExtendableVerifiedBy and ExtendableVerificationDate in the Sofas table, etc), if the verification really needs to be done separately for each column.