Flags in database - Sensible way of keeping track

Question

I am trying to design my first database and I have found that I have quite a few different "flags" that I want to keep of in the database:

Active      # Shows whether the item submission has been completed
Indexed     # Shows whether the item has been indexed
Reminded        # Shows whether the “expiring email” has been sent to the user
Error       # Shows whether there is an error with the submission
Confirmation    # Shows whether the confirmation email has been sent

Other than just having a Boolean field for these, is there a clever way of storing these details? I was wondering if I had these under a status group in the database with an ID for every connotation (32) and just link to that.

Answer 1

Unless there is some reason to do otherwise, I'd recommend simply adding those five boolean (or bit) columns to the item table.

Answer 2

It depends on how immutable the list of connotations is.

If there are just the five you mentioned then just add five flag columns. If the list of possible connotations could change in the future, it might be safer to have a separate table with a list of connotations that currently apply to each row in the main table, with a one-to-many relationship.

Answer 3

Consider:

Table: Vehicle
ID
Type
Doors
Color

Table: Type_Categories
ID
Name

Table: Types
TypeID
CategoryID
Value
DataType

This way reuse of type can occur in other places as needed. However this assumes non-boolean "Flags" if all flags are truly boolean... Id stick w/ putting them in the table. But I always hated boolean values. I preferred time stamps so I know when the flag was set not just that it was set. if the timestamp is null, then it's not been set.

Answer 4

In my experience the status columns frequently evolve to more than two states. So I would use a smallint for each status for convenience and simplicity.

But if your aim is to save space then you can save all the statuses in a single smallint using casts to and from bit to manipulate the statuses individually or as a whole.

create table t (status smallint);

To save 10010 then cast it to smallint:

insert into t (status) values (b'10010'::int::smallint);

List all statuses:

select status::int::bit(5) from t;
 status 
--------
 10010

To set the 3rd status use the bitwise or:

update t set status = (status::integer::bit(5) | b'00100')::integer::smallint;
select status::int::bit(5) from t;
 status 
--------
 10110

To unset that status use the bitwise and:

update t set status = (status::integer::bit(5) & b'11011')::integer::smallint;
select status::int::bit(5) from t;
 status 
--------
 10010

To retrieve the lines with the 3rd status set:

select status
from t
where substring(status::integer::bit(5) from 3 for 1) = '1'

You could write functions to simplify the conversions.

Answer 5

If they are "just" flags, store them as boolean-type columns on the table.

I'd recommend against Clodoaldo's solution unless space is REALLY tight - see this question.

It looks like the columns you mention have "business importance" - i.e. it may not be enough to store "Indexed", but also the date on which the item was indexed. It may be necessary to limit combinations of states, or impose rules on the sequencing (you can't go to complete whilst being in error state). In that case, you may want to implement an "item_status" table to store history etc.

In this case, your schema would be something like this:

ITEM
---------
item_id
....

STATUS
---------
status_id
description

ITEM_STATUS
--------------
item_id
status_id
date

Every time an item changes status, you insert a new row into the ITEM_STATUS table; the current status is the row with the latest date for that item.