Clever Geek Handbook

Database Design Preference: Using DateTime and BIT in SQL 2000

I need to explain this with an example:

Is there a best practice or preference for specifying DateTime and BIT in a database table?

My database has a Widget table. I need to know if the widget is "closed" and in "Closed Date" the business rules say that if the widget is closed it must have a closed date. If the widget is not closed, it should not have a "Closed Date".

To create this, I could do the following:

(example 1):

CREATE TABLE [Widget]
(
    [WidgetID] INT IDENTITY(1,1)
    ,[ClosedDate] DATETIME NULL
)

or (example 2):

CREATE TABLE [Widget]
(
    [WidgetID] INT IDENTITY(1,1)
    ,[IsClosed] BIT NOT NULL CONSTRAINT [DF_Widget_IsClosed] DEFAULT (0)
    ,[ClosedDate] DATETIME NULL
)

I think example 1 is cleaner because there is less to worry about the column. But, when I need to evaluate if the Widget is closed, I need an extra step to figure out if the ClosedDate column is NOT NULL.

Example 2 creates additional overhead because I now need to synchronize the IsClosed and ClosedDate values.

Is there a best practice when designing something like this? Will the query to the table be more efficient for example 2? Is there a reason why I should choose one design over the other?

Note. I would access this value using the ORM tool as well as stored procedures.

+2


source to share


4 answers


I think option 1 is better. Data integrity is better preserved (impossible to have a closed date with a flag that says otherwise), takes up less disk space in the case of large large tables, and queries will still be simple and easy to understand for teammates.



+5


source


The first is better. Checking for a null value is cheap, whereas keeping a separate flag allows you to have a date close but not close.



+2


source


I think the IsClosed column is a computed column.

CREATE TABLE [Widget](    
[WidgetID] INT IDENTITY(1,1),
[ClosedDate] DATETIME NULL,
IsClosed AS CAST(CASE WHEN ClosedDate IS NULL THEN 0 ELSE 1 END AS BIT)
)

The reason is that you are not storing anything and you can now code your application code and stored procedures to use this column. If your business rule ever changes, you can convert it to a real column so you don't need to change any other code. Otherwise, you will have business logic used throughout your application code and stored procedures. So it is only in 1st place.

Finally, when migrating to SQL2005, you can add a "Persisted" clause. This way, it will be stored, increasing performance slightly, and you won't have a sync problem.

+2


source


I would not assign a NULL semantic value. This will bubble through your business logic and you get code like ...

public class Widget
{
  // stuff

  public bool IsClosed
  {
    // what do you put here?
    // it was null in the db so you have to use DateTime.MinDate or some such.
    return( _closeDate == ?? );  
  }

  // more stuff
}

Using null in this way is bad. NULL (and null) means "I don't know". You are assigning semantic meaning to this answer when in fact you shouldn't. Closed status is closed state and closed date is closed date, do not combine them. (God forbid you ever want to open the widget again, but remember when it closed, for example.)

Eric Lippert has a nice blog post about the use of null (kidna).

0


source






More articles:


All Articles