Using a Single Row Configuration Table in SQL Server Database. Bad Idea

Using a Single Row configuration table in SQL Server database. Bad idea?

I have done this two ways in the past - a single row table and a key/value pair table - and there are positives and negatives to each approach.

Single Row

• positive: the values are stored in the correct type
• positive: it is easier to deal with in code (due to the above)
• positive: default values can be given to each setting individually
• negative: a schema change is required to add a new setting
• negative: the table can become very wide if there are lots of settings

Key/Value Pair

• positive: adding new settings does not require a schema change
• positive: the table schema is narrow, with extra rows being used for new settings
• negative: each setting has the same default value (null/empty?)
• negative: everything has to be stored as strings (ie. nvarchar)
• negative: when dealing with the settings in code, you have to know what type a setting is and cast it

The single row option is by far the easiest one to work with. This is because you can store each setting in its correct type in the database and not have to store the types of the settings as well as their lookup keys in code.

One thing I was concerned with using this approach was having multiple rows in the "special" single row settings table. I overcame this by (in SQL Server):

• adding a new bit column with a default value of 0
• creating a check constraint to ensure that this column has a value of 0
• creating a unique constraint on the bit column

This means that only one row can exist in the table because the bit column has to have a value of 0, but there can only be one row with that value because of the unique constraint.

SQL table with a single row?

I've seen something like this when a developer was asked to create a configuration table to store name-value pairs of data that needs to persist without being changed often. He ended up creating a one-row table with a column for each configuration variable. I wouldn't say it's a good idea, but I can certainly see why the developer did it given his instructions. Needless to say it didn't pass review.

I've just observed in some code I'm reviewing three different tables that contain three different kinds of certificates (a la SSL), each having exactly one row. I don't understand why this isn't made into one row; I assume I'm missing something.

This doesn't sound like good design, unless there are some important details you don't know about. If there are three pieces of information that have the same constraints, the same use and the same structure, they should be stored in the same table, 99% of the time. That's a big part of what tables are for fundamentally.

SQL Server: how to constrain a table to contain a single row?

You make sure one of the columns can only contain one value, and then make that the primary key (or apply a uniqueness constraint).

CREATE TABLE T1(
    Lock char(1) not null,
    /* Other columns */,
    constraint PK_T1 PRIMARY KEY (Lock),
    constraint CK_T1_Locked CHECK (Lock='X')
)

I have a number of these tables in various databases, mostly for storing config. It's a lot nicer knowing that, if the config item should be an int, you'll only ever read an int from the DB.

Is it a bad idea to have a property table in a database?

I've used a similar structure for storing property data, and I think it's fine as long as the table will remain relatively small. An entity-attribute-value (EAV) table like this may consume more space and exhibit slower query performance than a traditional column-structured table, but that shouldn't be an issue for a reasonably-sized set of application properties.

Is it a bad idea to reference a row from a many-to-many table in another many-to-many table

Its always been my impression that a many to many relationship is a violation Boyce-Codd Normal Form and therefore a violation of a good relational database schema.

Therefore, relating data to a link table is, infact, necessary to achieve BCNF and therefore good. If avoiding data update anomolies is good.

---

On to the specific schema example you presented. I think you want these logical tables (or entities),

-----------------------
EventClass
-----------------------
Id
Name
... Other attributes common to every instance
-
-----------------------
TimeSlot
-----------------------
Id
Start
End
-
-----------------------
Place
-----------------------
Id
Name
Address
MaxAttendance
... etc
-
----------------------
EventInstance
-----------------------
Id
EventClassId
TimeSlotId
PlaceId
PresenterName
...Other attributes specific to the instance

EventInstance is a realtionship between EventClass, TimeSlot and Place, any attributes specific to the EventInstance should be stored on that entity. Any attributes common to a related group of events should be stored on the EventClass attribute.

---

Its all a question of Database Normalization, generally speaking, the more normalized the data the better. However, there is a case for compromise when performance is a concern, if the desired data is stored in the output format it does make a select query simpler and faster although, updates might be hell.

I would counteract the case for compromise by suggesting that, with the right Indecies, Materialized Views and, indecies on Materialized Views, you can get the best of both worlds. The maintainability of fully normalized data with the speed of performance. Although, it does require some skill and consideration to get the schema right.

Best table design for application configuration or application option settings?

For config data, I'd use the key/value structure with a row per configuration entry. You're likely to read this data once and cache it, so performance isn't an issue. As you point out, adding columns each time the set of config keys changes requires a lot more maintenance.

SQL excels at modeling and manipulating arbitrarily large sets of similarly (if not the same) structured data. A set of configuration information really isn't that -- you've got a single row of data OR you've got multiple rows of completely unrelated data. That says you're just using this as a data store. I say skip the SQL data model and go simple.

Constants in application configuration file versus single row in a database table

I suppose that in the sample you saw the author wanted to use those values inside of the db too. For instance he could use them inside a joined view or for generating user defined db-function to calculate things. In this case he needs those values in the db.

If you seperate your logic from the db (which I heavily advice you to do!) then you should avoid this pattern strictly and make use of constants and configurations (natural values like miles to kilometers and others should be constants, values changing over time should be configs).

Constructs like code first will take the decision away from you :-).

Is having multiple SQL db's a bad idea for a single application?

Certainly not anything inherently "bad" about this approach. In fact, it's often a good idea and in your case sounds like it probably is. You can possibly get performance gains depending on how you create and open the various databases as well.

A couple specific pointers:

• Static Data: Since this database is read-only, open it as read-only
• You can can actually join across databases like: Use the ATTACH DATABASE SQL statement and go from there.

SQL Server: the maximum number of rows in table

It's hard to give a generic answer to this. It really depends on number of factors:

• what size your row is
• what kind of data you store (strings, blobs, numbers)
• what do you do with your data (just keep it as archive, query it regularly)
• do you have indexes on your table - how many
• what's your server specs

etc.

As answered elsewhere here, 100,000 a day and thus per table is overkill - I'd suggest monthly or weekly perhaps even quarterly. The more tables you have the bigger maintenance/query nightmare it will become.

---

Related Topics