Flags in database - Sensible way of keeping track

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.

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.

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.

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.