Is this schema design good?

Question

I inherited a system that stores default values for some fields in some tables in the database. These default values are used in the application to prepopulate control values. So, essentially, every field in every table in the database can potentially have a default value. The previous developer decided to store these values in a single table that had a key/value pair combo. The key represented by the source table + field name (as a varchar) and the default value as a varchar field as well. The Business layer would then cast the varchar field to the appropriate data type.

Somehow, I feel this is brittle. Though the application works as expected, there appears to be a flaw in the design.

Any suggestions on how this requirement could have been handled earlier? Is there anything that can be done now to make it more robust?

EDIT: I should have defined what the term "default" meant. This is NOT related to the default value of a field in the table. Instead, it's a default value that will be used by the application in the front end.

Answer 1

A better way to go would be using SQL Server's built-in DEFAULT constraint.

e.g.

CREATE TABLE Orders
(
    OrderID int IDENTITY NOT NULL,
    OrderDate datetime NULL CONSTRAINT DF_Orders_OrderDate DEFAULT(GETDATE()),
    Freight money NULL CONSTRAINT DF_Orders_Freight DEFAULT (0) CHECK(Freight >= 0),
    ShipAddress nvarchar (60) NULL DF_Orders_ShipAddress DEFAULT('NO SHIPPING ADDRESS'),
    EnteredBy nvarchar (60) NOT NULL DF_Orders_EnteredBy DEFAULT(SUSER_SNAME())
)

Answer 2

If you want (and should!) use default values on the database, I would strongly urge to use the built-in DEFAULT constraint that's available on any field. Only that is really guaranteed to work properly - anything else is a hack solution at best.....

CREATE TABLE 
  MyTable(ID INT IDENTITY(1,1),
          NumericField INT CONSTRAINT DF_MyTable_Numeric DEFAULT(42),
          StringID VARCHAR(20) CONSTRAINT DF_MyTable_StringID DEFAULT 'rubbish',
      .......)

and so on - you get the idea.

Just learn this mantra: DRY - DON'T REPEAT YOURSELF - don't go out re-inventing stuff that's already there and has been heavily tested and used - just use it.

Marc

Answer 3

Refactoring the schema now would be risky and disruptive so I would not recommend it (unless you absolutely need to do that to fix some pressing issue, but from what you say it doesn't look like you do).

Were you doing the design from scratch, I'd recommend one defaults-table per real-table, with a single row recording the defaults with their real column names and types. Having several tiny tables scares some DBAs, but it's not really any substantial performance hit in my experience, and it sure does make the system sounder and more robust as you desire.

If you want to use SQL's own DEFAULT clauses as other answers recommend, be sure to name those explicitly, otherwise altering them when a default changes can be a doozy. Personally, I like to keep the default values separate from the schema's metadata, especially in a system where updating or tweaking a default value is a much more common and should-be-innocuous operation than the momentous undertaking of metadata/schema changes!

Answer 4

If its a case of UI defaults - the following questions come up.

• How 'dynamic' or generic is your schema.? Does the same schema support multiple front-ends - i.e. the same column in the Db-table supports 2 front-ends - each with multiple-defaults?
• Do multiple apps use your DB? In that case having the default defined in the DB could still help
• Its possible to query the Data-dictionary to get default info for each column.
• If a UI field does not have a corresponding db-column, then your current implementation will be justified in such cases
• One downside is more code is needed to handle and use this table.
• If it was a one-off application and this default 'intelligence' was not leveraged across multiple-apps - thats a consideration
• Its more like a 'frameworky' kind of thing to do - though I'd say its quite non-standard, and would be done on the web-layer.

Answer 5

If the requirement was that the default selection of a given control be configurable and the "application works as expected" then I don't see a problem. You didn't elaborate on the "flaw" in the design.

Answer 6

I think the real answer here depends heavily on how often these default values change. If default values are set once when the database is designed, then DEFAULT constraints make sense. If some non-technical person needs to change them every couple of months, I really like the design presented.

Where it becomes brittle is when you have a mismatch between the column names or data types and the default values in the Defaults table. If you code a careful interface to manage the Defaults table values, this shouldn't be a problem.

Answer 7

The idea (not necessarily the implementation) makes sense if you want to keep the application defaults separate from the data, allowing different apps to have different defaults.

This is generally a good thing, because many databases inevitably spawn secondary applications (import jobs, if not anything else), where you do NOT want the same defaults (or any defaults at all); and in principle, a defaults table can support this.

What I think makes this implementation less-than-ideal is that while the defaults are MOSTLY data-driven, the calling application either needs its own set of defaults IF the defaults are not specified in the table or terminate.

If the former is employed, this could introduce a number of headaches when you're trying to track down bugs, especially if you don't have good audit tables keeping track of which user/application inserted/updated which rows on which tables.

Disclaimer: I'm generally of the thought that columns ought to be NULLable and w/o defaults, except where it absolutely makes sense from a data point of view (id/primary key, custom timestamp, etc.). If a column should never be NULL introduce a constraint forbidding NULLs, not a concrete default.

Answer 8

If the table of default values is what irks you, here's some food for thought:

Rather than stick to dogma about varchar(max) or casting strings or key/value tables - a good approach is to ask what is a better solution?

From your description, it seems like this table contains few rows, and has only two columns: key and value.

I should ask - is the data in this table controlled from an administrative UI? Perhaps this is the reason behind the original design decision to make it a table.

If type-safety is an issue, you could consider the existence of a "type" column and analyze how the code would need to be changed.

I wouldn't jump to conclusions about "good" or "bad" until you really analyze WHY the system is implemented this way.

Answer 9

That schema design is fine. I've seen it used in commercial apps and I've also used it in a few apps of my own where the users needed to be able to change the defaults or other parameters around fields in the application (limits, allowable characters etc.) or the application allowed the users to add new fields for use in the app.

Having it in a single table (not separate default tables for each table) protects it from schema changes in the tables it supports. Those schema changes become simple configuration changes in this model.

The single table makes it easy to encapsulate in a Class to serve as the "defaults" configuration object.

Some general advice: When you inherit a working system and don't understand why something was designed the way it is - the problem is most likely your understanding, not the system. If it isn't broken, do not fix it.

Specific advice on the only improvements I would recommend (if they become necessary): You can use the new SQLVARIANT field for the value rather than a varchar - it can hold any of the regular data types - you will need to add support for casting them to the correct data type when using the value though.

Ron