SQL Server Database Design – Storing Calculations Efficiently

database-designsql server

I am in the process of designing a database structure to store some calculation information.

Information about the problem:

  • Each "calculation" is a collection of scripts allowing to calculate value, target, low limit and high limit. All of those are optional.

  • A calculation script is a string, that will be parsed by the client app. Example: 50 + {OutsideTemperature}

I have tried to follow the principles of normalization, as I understood those, and I came up with:

CREATE TABLE CalculationScript(
ScriptId int NOT NULL PRIMARY KEY,
Script VARCHAR(8000) NOT NULL
);

CREATE TABLE Calculation(
CalculationId int NOT NULL PRIMARY KEY,
Name nvarchar(100) NOT NULL
);

CREATE TABLE ValueScript(
CalculationId int FOREIGN KEY REFERENCES Calculation(CalculationId),
ScriptId int FOREIGN KEY REFERENCES CalculationScript(ScriptId)
);

CREATE TABLE TargetScript(
CalculationId int FOREIGN KEY REFERENCES Calculation(CalculationId),
ScriptId int FOREIGN KEY REFERENCES CalculationScript(ScriptId)
);

CREATE TABLE LowLimitScript(
CalculationId int FOREIGN KEY REFERENCES Calculation(CalculationId),
ScriptId int FOREIGN KEY REFERENCES CalculationScript(ScriptId)
);

CREATE TABLE HighLimitScript(
CalculationId int FOREIGN KEY REFERENCES Calculation(CalculationId),
ScriptId int FOREIGN KEY REFERENCES CalculationScript(ScriptId)
);

The table will be used as a configuration table. The client app will query the calculation, parse the scripts and run the calculation. For example, the calculation:

Name: testCalc
Value: 50 + {OutsideTemperature}/2
LowLimit: 10 - 5^({OutsideTemperature})

Will yield, at runtime, if it is 0°C outside

Name: testCalc
Value: 50
LowLimit: 9

And if it is 5°C outside:

Name: testCalc
Value: 52.5
LowLimit: -3115

Before moving forward with it, I would like some feedback on this structure, and in particular, is it overkill to create so many tables for such a simple problem?

As there is 1-1 relations all across the chain, having one table would obviously be easier, but I am worried about having a mostly empty table full of varchar(8000) fields.

Best Answer

I would just have a single table to store the relationships, just add a ScriptType and then you have the classic Entity Attribute Value table. Properly indexed (an example is below) the performance should be pretty good. Then adding additional types is straightforward. Not sure if it makes more sense for you to have the ScriptType attribute as a component of the CalculationScript table or not, but I would definitely go with a single table. 

CREATE TABLE CalculationScript(
ScriptId int NOT NULL PRIMARY KEY,
Script VARCHAR(8000) NOT NULL
);

CREATE TABLE Calculation(
CalculationId int NOT NULL PRIMARY KEY,
Name nvarchar(100) NOT NULL
);

CREATE TABLE ScriptType(
ScriptTypeID INT NOT NULL PRIMARY KEY,
[Name] NVARCHAR(100) NOT NULL
);

CREATE TABLE Script_EAV(
Script_EAVID INT NOT NULL PRIMARY KEY IDENTITY(1,1),
CalculationId int FOREIGN KEY REFERENCES Calculation(CalculationId),
ScriptTypeID INT FOREIGN KEY REFERENCES ScriptType(ScriptTypeID),
ScriptId int FOREIGN KEY REFERENCES CalculationScript(ScriptId)
)

CREATE NONCLUSTERED INDEX IDX_Script_EAV_CalclationID_ScriptTypeID ON dbo.Script_EAV
    (CalculationID, ScriptTypeID)
    WITH (FILLFACTOR=70, SORT_IN_TEMPDB=ON)
Related Question