CODEWITHSUNDEEP
mysqlsqldatabase
Ingo
Ingo

Reputation: 116

MySQL Three State Attribute

This is a question of good database design as off the top of my head, I can think of many ways to implement this, but the approaches I am coming up with are most likely poor database design (I do not want to be scolded by C.J. Date). I would like to set up a table in my MySQL database for inspection items that are part of an inspection form. The inspection form can be saved multiple times by the user before having completed all inspection items. Each item can therefore be in an "un-inspected" state, or in an "inspected" state. If an inspection item has been inspected, it must have a "pass" or "fail" value.

Originally I was going to use a single boolean attribute that allows NULL, and I was going to have NULL represent the "un-inspected" state, thinking that NULL means the value of the attribute (pass/fail) is unknown. But watching videos and reading books on database design, I am led to believe that the use of NULL to represent a state or value is poor design. I do not think using two attributes (inspected & pass) or using a three valued ENUM would be good database design either.

This may be a very elementary problem, and I may just be overcomplicating things.

Upvotes: 3

Views: 558

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

I think there is no inherent problem having a single column for "pass"/"fail" with NULL meaning that the status has not been assigned. In fact, I can see a certain elegance to it, assuming that the values are well-documented, so users know what they are getting.

You can also use an enum. To be honest, though, when using enums I often have to look up what values are acceptable (is it "pass" or "passed"?). A bit/boolean type makes that process much easier.

So, I wouldn't stress over this particular problem. If

  • 1=pass
  • 0=fail
  • NULL=in progress

Then that is a reasonable interpretation. If things get more complicated, you might want to use a reference table (or enums).

Upvotes: 3

Related Questions