CODEWITHSUNDEEP
sqlschema
Jeremy
Jeremy

Reputation: 5435

SQL Schema design-- using nullable fields to replace boolean columns

Let's take a table where we store messages that customers have sent us. Let's say, then, we also want to store in that table if a customer has requested that we send them a response to a correspondence they sent us. If they HAVE requested a response, then we store that information by recording their e-mail address and date expected. We have no need of their e-mail address if they don't want a response.

Correspondences
    Primary_key
    Content
    Date
    Response_expected
    Return address
    Return date

Alternatively, we can get rid of the response_expected column entirely and agree that a null return address/return date means "no response is expected", and a non-null one means that a response is expected. The application would be responsible for using this logic rather than checking an explicit "Do we want a response?" field.

(Assume for the sake of this question that a constraint is in place so that return address and date must be null unless response expected is yes... my point is this column won't be misused or used for another purpose)

My question is: Is this kind of design, where the application has to agree to a specific way of interpreting the data, acceptable? On the other hand we are storing more data than we need since we will definitely not have a return address and date if they are not expecting a response

Upvotes: 0

Views: 74

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

I would have a separate column for Response_Expected, just as you have it now. Yes, you might have redundant information because the return address would be NULL when no response is expected.

However, the two ideas are different. here are some scenarios that you might want to consider:

  • In the future, you have more than one way to return a response. Email is only one channel. This will complicate the data structure, but the logic will rely on a single channel.
  • You send an email, but it bounces or is not deliverable for some other reason.
  • Responses become dependent on some other criteria. They want a reponse, but only under certain conditions.

I wouldn't worry about the duplication of data. I would think more about the sustainability of the application.

Upvotes: 2

Related Questions