CODEWITHSUNDEEP
mysqlsqlorm
Bishara
Bishara

Reputation: 95

Convention for boolean values in databases

I'm making a database for the first time, which is exciting, but my problem comes in the form of the convention of boolean or binary variables. Within my database, a user makes a request from the server that takes a few hours to resolve (because a human on the other end has to interact with it). There are a few ways to label the opened or closed request. A column called open could be set to True or False or I could make a column called status filled with strings "open" or "closed". Is there a convention for this or am I being pedantic?

Upvotes: 0

Views: 404

Answers (2)

DavidBliss
DavidBliss

Reputation: 1

We normally use data type of bit, True, false. The column could be called Opened...with the default value of ((0)), so it is False by default. Then when action has been taken, you can manually change it to True, or build a page that the person can "flip the switch" so it is now True. As the post below mentions, adding a table to record these changes would be idea to create a "Status History", with the user who changed it, what they changed it too, and datetime information.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270051

You are not being pedantic, and it is good to design the database in advance based on how it is going to be used.

My recommendation is to have a StatusChanges table. Each time the status changes, you would have (at least):

  • A unique id for the status change record
  • The account being affected
  • The new status (and perhaps the old status)
  • Date/time stamp
  • Person making the change

The ability to get the current status for any user might be important. If so, you can store that information in the user record. Alternatively, you can do a somewhat complicated query on the StatusChanges table. Or, you could turn it into a slowing changing dimension by having an effective and end date for each record.

The key point that I want to make is that you should keep the history with the important characteristics of the history.

Upvotes: 2

Related Questions