Prefer richer data to booleans

This is part of the Semicolon&Sons Code Diary - consisting of lessons learned on the job. You're in the databases category.

Last Updated: 2022-12-01

Foreign Key is more useful than boolean

In Project S, we had tickets which were used to book appointments. Initially I set the ticket boolean column used to true when a booking was made (but never tracked which ticket was used for which appointment).

Later requirements meant this info needed to be tracked so I added a ticket_id field to appointments. This had the side-effect that the used boolean was redundant, since the same info was available via the ticket_id and a query of the form

SELECT * 
FROM tickets 
WHERE NOT EXISTS (
  SELECT ticket_id 
  FROM appointments
  WHERE tickets.id = appointments.ticket_id
)

I sort of saw this coming and I should have went with my gut to use the foreign key (FK) instead of the boolean.

Date is more useful than boolean

In most use-cases for standard web-apps it's more useful to have published_at with a timestamp than a simple boolean.