CODEWITHSUNDEEP
databasedatabase-design
acadavid
acadavid

Reputation: 506

Table with many boolean "status" fields

I have a table which represent an object that has multiple "status" (approved, expired, trending, real). All these status are independent, so i can't have like a single status field because queries can be made so you can ask:

select * from Objects WHERE approved is true, trending is true, expired is false;

I'm not sure if there is anyway to normalize such table. Also, from what i understand, using indexes for those fields would be basically useless. Is that right for this case also?

Thanks!

Upvotes: 3

Views: 2367

Answers (1)

Thomas
Thomas

Reputation: 64645

The argument against lots of boolean status columns isn't due to space or even normalization per se. If each column relates entirely to the key and does not consist of an amalgam of values, it is normalized enough (3rd normal). Rather, the issue is with the boolean nature of the columns and their lack of expansion. For example, you showed us a column called approved. What about the date it was approved and by whom? What happens if the approval pipeline expands to include more states than just approved/not approved such as initiated, pending, approved and rejected? What about the description for the approval status? Today it might Y/N but tomorrow your customers might want Approved/Rejected or perhaps Qui/Non.

I'm not suggesting you turn your design on its head; boolean columns have their place. However, they should be used after careful consideration about expansion and extensibility.

Upvotes: 6

Related Questions