CODEWITHSUNDEEP
sqlpostgresqldatabase-design
VSO
VSO

Reputation: 12646

Boolean Look up Tables - Bool Column or Simple Lookup?

We need a table to store requests for a copy of a contract, let's say contract_copy_requests.

The user either made a request, or did not. The field is unlikely to ever toggle.

Which of these column options is "correct":

  1. profile_id, request date (and only write ids that made a request in here) OR

  2. profile_id, has_requested_copy, updated_date

The latter is more complete and extensible, but the former is simpler and doesn't require either a nullable field or backfilling existing data with false for everyone since they haven't made the choice yet.

Is there any real reason to do #2? Btw, this is more of a generic question - I have had this use case come up about 3 times in the past month and I always lean toward #1 but wanted to validate it.

Upvotes: 0

Views: 201

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246578

The first option is better.

It is smaller, and you lose no functionality: for example, it will be easy to get the users that didn't make a request:

... WHERE NOT EXISTS (SELECT 1 FROM contract_copy_requests AS ccr
                      WHERE ccr.request.id = /* column from the outside */)

Upvotes: 1

Related Questions