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

In developing a shopping cart application I've found that I needed to save settings and configurations based on the administrator's preferences and requirements. This information can be anything from company information, Shipping account IDs, PayPal API keys, notification preferences, etc.

It seems highly inappropriate to create a开发者_如何转开发 table to store a single row in a relational database system.

What is the appropriate way to store this information?

Note: my DBMS is SQL Server 2008 and programming layer is implemented with ASP.NET (in C#).

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.

You should create a table with a column for the information type and information value (at least). This way you avoid having to create new columns every time a new information is added.

A single row will work fine; it will even have strong types:

show_borders    bit
admin_name      varchar(50)
max_users       int

One disadvantage is that it requires a schema change (alter table) to add a new setting. One alternative is normalizing, where you end up with a table like:

pref_name       varchar(50) primary key
pref_value      varchar(50) 

This has weak types (everything is a varchar), but adding a new setting is just adding a row, something you can do with just database write access.

Personally, I would store it in a single row if that is what works. Overkill to store it in an SQL table? probably, but there is no real harm in doing so.

As you guessed, and except for the simplest situations, putting all configurations parameters in a single rows has many drawbacks. It is a bad idea...

A convenient way to store configuration and/or user preference type of information is in XML. Many DBMSes support the XML data type. The XML syntax allows you to expend the "language" and structure describing the configuration as this configuration evolves. One advantage of XML is its implicit support for hierarchical structure, allowing for example to store small lists of configuration parameters without having to name these with a numbered suffix. A possible drawback of XML format is that searching and generally modifying this data isn't as straight forward as other approaches (nothing complicated, but not as simple/natural)

If you want to remain closer to relational model, the Entity-Attribute-Value model is probably what you need, whereby the individual values are stored in a table that typically looks like:

EntityId     (foreign key to the "owner" of this attribute)
AttributeId  (foreign key to the "metadata" table where the attribute is defined)
StringValue  (it is often convenient to have different columns of different types
IntValue      allowing to store the various attributes in a format that befits 
              them)

Whereby the AttributeId is a foreign key to a table where each possible Attribute ("configuration parameter" in your case) is defined, with say

AttributeId  (Primary Key)
Name
AttributeType     (some code  S = string, I = Int etc.)
Required          (some boolean indicating that this is required)
Some_other_fields   (for example to define in which order these attributes get displayed etc...)

Finally the EntityId allows you to identify some entity which "owns" these various attributes. In your case it could be a UserId or even just implicit if you only have one configuration to manage.

Aside from allowing the list of possible configuration parameters to grow as the application evolves, the EAV model places the "meta data", i.e. the data pertaining to the Attribute themselves, in datatables, hence avoiding all the hard-coding of column names commonly seen when the configuration parameters are stored in a single row.

You certainly don't have to change your schema when adding a new configuration parameter in the normalized approach, but you're still probably changing your code to process the new value.

Adding an "alter table" to your deployment doesn't seem like that big of a tradeoff for the simplicity and type safety of the single row approach.

A Key and Value pair is similar to a .Net App.Config which can store configuration settings.

So when you want to retrieve the value you could do:

SELECT value FROM configurationTable
WHERE ApplicationGroup = 'myappgroup'
AND keyDescription = 'myKey';

A common way to do this is to have a "properties" table simmular to a properties file. Here you can store all your app constants, or not so constant things that you just need to have around.

You can then grab the info from this table as you need it. Likewise, as you find you have some other setting to save, you can add it in. Here is an example:

property_entry_table

[id, scope, refId, propertyName, propertyValue, propertyType] 
1, 0, 1, "COMPANY_INFO", "Acme Tools", "ADMIN"  
2, 0, 1, "SHIPPING_ID", "12333484", "ADMIN"  
3, 0, 1, "PAYPAL_KEY", "2143123412341", "ADMIN"   
4, 0, 1, "PAYPAL_KEY", "123412341234123", "ADMIN"  
5, 0, 1, "NOTIF_PREF", "ON", "ADMIN"  
6, 0, 2, "NOTIF_PREF", "OFF", "ADMIN"   

This way you can store the data you have, and the data that you will have next year and don't know about yet :) .

In this example, your scope and refId can be used for whatever you want on the back end. So if propertyType "ADMIN" has a scope 0 refId 2, you know what preference it is.

Property type comes in hand when, someday, you need to store non-admin info in here as well.

Note that you should not store cart data this way, or lookups for that matter. However if the data is System specific, then you can certainly use this method.

For example: If you want to store your DATABASE_VERSION, you'd use a table like this. That way, when you need to upgrade the app, you can check the properties table to see what version of your software the client has.

The point is you do not want to use this for things that pertain to the cart. Keep you business logic in well defined relational tables. The properties table is for system info only.

You can do the Key/Value Pair without conversions by adding a column for each major type and one column telling you which column the data is in.

So your table would look something like:

id, column_num, property_name, intValue, floatValue, charValue, dateValue
1, 1, weeks, 51, , ,
2, 2, pi, , 3.14159, , 
3, 4, FiscYearEnd, , , , 1/31/2015
4, 3, CompanyName, , , ACME, 

It uses a little more room but at most you are using a few dozen attributes. You can use a case statement off the column_num value to pull / join the right field.

Have a key column as varchar and a value column as JSON. 1 is numeric whereas "1" is a string. true and false are both boolean. You can have objects as well.

I'm not sure a single row is the best implementation for configuration. You might be better off having a row per configuration item with two columns (configName, configValue), although this will require casting all of your values to strings and back.

Regardless, there's no harm in using a single row for global config. The other options for storing it in the DB (global variables) are worse. You could control it by inserting your first configuration row, then disabling inserts on the table to prevent multiple rows.

Sorry I come like, yeaars later. But anyways, what I do is simple and effective. I simply create a table with three () columns:

ID - int (11)

name - varchar (64)

value - text

What I do before creating a new config column, updating it or reading is to serialize the "value"! This way I am sure of the type (Well, php is :) )

For instance:

b:0; is for BOOLEAN (false)

b:1; is for BOOLEAN (true)

i:1988; is for INT

s:5:"Kader"; is for a STRING of 5 characters length

I hope this helps :)