Is it bad practice to use default values in a database?

Question

Shouldn't the code deal with default values instead of the database?

Answer 1

That depends on how you think of the "default" value. Think of it this way: What should happen if you change the default? If existing values should be updated, then the default should be in the program code only, but if the existing values should remain, then you should store the default in the database.

Answer 2

I generally believe code should take care of default values. This will help keep your DB lean and mean. Although, if it seems your DB has an excessive amount of empty fields you may want to rethink the design.

EXAMPLE

Imagine you have a table with a million or more rows. In this table you have a datetime column that is filled in maybe 5% of the time. As time goes on the amount of space you'll save by storing a NULL default will make the necessary default checks more than worth it.

Answer 3

Anything you can do in the database is typically more robust. If you handle default values which will be used if no value has been specified only in your app, and someone manages to connect to your database some other way than via your app (and believe me - the users WILL try to connect using Excel or other tools) - then the database is wide open and the user could potentially insert crappy data.

Same goes for referential integrity and check constraints. I believe you're better off if you try to have as many of those constraints on the database - then no matter how the user connects, if he doesn't send you anything, you can put in sensible defaults.

Don't let the application handle your checks - leave that to the database!

Plus it makes your SQL insert statement a lot leaner and meaner if you don't have to specify all the "obvious" defaults, like "getdate()" for a "LastChangedOn" date column etc.

Answer 4

Just the opposite. The database should always provide default values that ensure that records added are valid and meaningful. Remember, you cannot predict at the time you write the application what code will eventually be adding records to the database.

That's not to say that you may not have a second level of default data specified by the application that depends on the application state at the time the data is created.

So, imagine that you have an employee status code that can be "P" (propect), "A" (active), "T" (terminated), or "R" (retired). You should specify at the database level whether people enter the system as "P" or "A", for instance (or perhaps a fifth code for Unassigned). But your application can and should require the user creating the employee record to select one of the options from (for instance) a group of radio buttons and use that value when INSERTing the record.

Answer 5

You may want to consider what is required to change the defaults. If you application is in house it may not be difficult to change, but if it is on your customers site, then making changes to the database can be a VERY difficult process. If you have hundreds or thousands of clients and you need to convince their DBAs to give you update process SA access to the database you will regret putting any application logic in the DB (including defaults).

Answer 6

As with most "code vs database" arguments, it depends.

If your database (or table) is going to be accessed by a lot of different customers (i.e. teams who don't work closely together), then the database should protect itself. Ideally, everything should be done via stored procedures.

If a single application has access to the database, then that application can contain the business logic. In this case, everything should be done by the one application, and that application accesses the database.

Also: If you want a cheap answer - ask the person who is in charge of specifying (and documenting) your data models. If you don't have one, start panicking.

Answer 7

Code defaults are easier for unit testing.

Code defaults support multiple scenarios. DB column defaults are a one size fits all. For example, the DB column defaults may vary depending on customer type.

DB column defaults are often opaque to the maintenance developer because they are far away from the INSERT statement, which is usually in the middle tier code, stored procedure, etc. The presence or absence of defaults can be surprising, either way.

DB column defaults defend the database against clients that are too lazy to fill in defaults at all, which is a form of data corruption.

Both kinds of defaults can be subverted by client developers. It is easier to set up barriers to developers using defective defaults in the middle tier. AFAIK, no database lets you require a field take the default value on INSERT. An example of where this might matter is the various tokens people use for a unknown but future date, or an unknown but past date, or it might matter if they use GETDATE() which includes time or if they use a default date with year, month, day and no time.

I'd recommend making sure defaults exist where they make sense in the DB, but don't actually use them. The DB defaults should be the defaults of last resort, and defaults should be solidly in a middle tier (i.e. stored procs, a data access layer). A DB column default is like an exception handler-- when someone forgot by accident to provide a value, what value should be used to prevent data corruption?

Answer 8

If you are concerned about data integrity (And if you aren't why do you have a database?), you need the default values in the database where they belong. To do this only in code is irresponsible. Data gets into databases from other sources than application code.

Answer 9

Databases are intended to record assertions of fact.

To allow a user to make incomplete assertions and to have the dbms make silent assumptions about the incomplete part, is plain bad.

If you want robust, reliable systems that can be kept in use for longer times, then don't let any part of that system ever make assumptions about what the user does not say.

Handle defaults at the presentation level and nowhere else. And handle them in such a way that there is no way for the user to not have seen the data he submits (ALL the data !).

Answer 10

Unless the value is completely decoupled from your business logic, I would say yes, it is bad practice. At least according to the tiered model, where the business layer is separate from the data layer.

Remember that:
- Putting a default value on a column does not guarantee data integrity (an application should be able to handle any value put in the column)
- Going to the trouble and expense of somehow duplicating your business logic in the database doesn't guarantee data integrity.

The most sane approach to database security is to have just that -- database security. If a malicious user, for example, has managed to break that security layer, then your data doesn't stand a chance, regardless of the data constraints.