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.
Part of our DB design
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...

  • I'm not quite clear on what you're trying to do. Can you expand with some draft tables, or a more concrete example? Jun 11 '12 at 15:09
  • @JonofAllTrades I edited the question, I think is much more clear now.
    – eversor
    Jun 12 '12 at 9:04

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:

    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:

    CreatedBy   INT NOT NULL
    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.

  • Ok, now imagine that instead of having n tuples in Searches you only have 1 because you only have an user and allways is going to be 1... then, that's my problem... the config values I have to store have an unique occurrence that would change at user (singular) will.
    – eversor
    Jun 12 '12 at 18:04
  • You can certainly store just one record in Searches per user, though if so you may just want to add search preference fields to the Users table. How is this a problem? Jun 12 '12 at 18:40
  • The problem is that there is just 1 unique user with options. And that user, lets call it now "program" (it is more clear) have no representation yet on the DB. So if it had it would be a relationless table with just one tuple.
    – eversor
    Jun 12 '12 at 18:52
  • So there will only ever be one program, reporting on the many users? With just one record in its hypothetical SearchTerms table? Then how you store it hardly matters, since you're talking about just a few bytes anyway. I must be missing something. Jun 12 '12 at 22:22
  • 1
    @eversor - Jon of All Trades is correct (+1). Configuration settings belong in a configuration settings table, or outside of the database. Where you are going wrong is in thinking that the number of notifications needed is an attribute of Notifications. This is, in my opinion, one of the reasons why it is bad to use plural nouns for table names in general. The Notifications table is not the place to put everything about notifications, it is the place to store a set of individual notification records. Your setting is an attribute of the system user, not of notifications.
    – Joel Brown
    Jun 14 '12 at 11:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.