Configuration Persistance Design

Question

We have a well designed and normalized DB, but, there is a requisit that could cloud this design.
This image shows the design of part of the DB.

The main functionality of our application is to create some stats of the quantity of notifications each user recieves. So we query the DB by groups of users (e.g. SELECT COUNT(*) FROM Notifications INNER JOIN Users ON User.UserID = Notifications.UserID WHERE User.att = ...).

The requisit that is causing me troubles is that our application allows a user (which is different of any user stored in the table Users, since this is a monitoring application) to customize some configuration parameters, such as miliseconds to wait before looking again in the DB in order to obtain some data for charts, which number of notifications makes a user selectable (e.g. if the user has more than X notifications then it should be displayed in the chart) etc.

This configuration must be persistant, we do not want to make the user set the parameters each time he opens the application.

The problem I see is, how do I make the configuration persistant without clouding the design. Because, if you set, lets say, the X number of notifications needed, you could see that as an attribute of Notifications, but is kind of an Entity attribute. Since, is (conceptually) an attribute of Notifications but what we store there are notifications, which are oblivious of configuration attributes.

Moreover, we have more entities, with exactly the same problem. An entity that stores certain kind of data, and from the application you can configure in which ways this data is going to be treated. And that configuration must be persistant.

Therefore, my question is, what is the appropiate way to represent this configuration persistance? Should it be represented in the DB or in other file? I think that it should be stored in the DB, but maybe I am being stubborn...

Answer 1

I'd say it depends on how many parameters you have, and whether this list is expected to be largely static or if it's likely to change or expand.

I'm still a little fuzzy on exactly what you're doing, so I'm going to answer a slightly different question. Let's say you're writing a query engine, searching for users that meet different requirements, and you want to store a set of predicates so it can be reused. In a simple world, your table might look like this:

CREATE TABLE Searches
    (
    ID  INT NOT NULL PRIMARY KEY,
    CreatedBy   INT NOT NULL,  -- Prob also CreatedDate, LastModified, etc.
    FirstName   VARCHAR(100),
    LastName    VARCHAR(100),
    Sex CHAR(1),
    EMailAddress    VARCHAR(100),
    Country INT
    )

Some day you'll need to add a Region field when you start storing more precise geographic data. Then you'll add PhoneNumber, and then BloodType, and then... eventually, you'll need something more flexible. You need a table for searches and a table to store key-value pairs for each search:

CREATE TABLE Searches
    (
    ID  INT NOT NULL PRIMARY KEY,
    CreatedBy   INT NOT NULL
    )
CREATE TABLE SearchTerms
    (
    ID  INT NOT NULL PRIMARY KEY,
    SearchID    INT NOT NULL FOREIGN KEY REFERENCES Searches (ID),
    FieldName   VARCHAR(100) NOT NULL,
    Value   VARCHAR(100) NOT NULL,
    UNIQUE (SearchID, FieldName)
    )

The UNIQUE constraint may not be appropriate. For example, it may be appropriate to have a search with Terms { FieldName = "FirstName", Value = "Bo%" } and { FieldName = "FirstName", Value = "ob%" }.

This more flexible approach loses type checking; searching for "NumArms = 2" now requires storing "2" in a VARCHAR field. It also implies using dynamic SQL to construct your searches.