Flags in the Database - The Smart Way to Track
I am trying to create my first database and I found that I have quite a few different "flags" that I want to store 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
Besides the fact that they have a boolean field, do they have a clever way to store this data? I was wondering if I have this in a status group in the database with an ID for each connotation (32) and just a link to that.
Any ideas or input would be much appreciated :)
source to share
It depends on how immutable the list of connotations is.
If there are only five you mentioned, just add the five flag columns. If the list of possible connotations may change in the future, it may be safer to have a separate table with the list of connotations currently applied to each row in the main table with a one-to-many relationship.
source to share
Consider:
Table: Vehicle
ID
Type
Doors
Color
Table: Type_Categories
ID
Name
Table: Types
TypeID
CategoryID
Value
DataType
Thus, type reuse can occur elsewhere as needed. However, this assumes non-boolean "flags" if all flags are indeed boolean ... Id stick w / put them in a table. But I've always hated logical values. I preferred timestamps, so I know when the flag was set, not just for it to be set. if the time stamp is zero, then it has not been set.
source to share
In my experience, status columns often evolve into more than two states. So for convenience and simplicity, I would use smallint
for each state.
But if your goal is to save space, you can keep all statuses in one smallint
, using casts to and from bit
to manage statuses individually or as a whole.
create table t (status smallint);
To save 10010
, highlight it in smallint:
insert into t (status) values (b'10010'::int::smallint);
List of all statuses:
select status::int::bit(5) from t;
status
--------
10010
To set the third status use bitwise or
:
update t set status = (status::integer::bit(5) | b'00100')::integer::smallint;
select status::int::bit(5) from t;
status
--------
10110
To disable this status use bitwise and
:
update t set status = (status::integer::bit(5) & b'11011')::integer::smallint;
select status::int::bit(5) from t;
status
--------
10010
To get rows with the 3rd set of states:
select status
from t
where substring(status::integer::bit(5) from 3 for 1) = '1'
You can write functions to facilitate conversions.
source to share
If they are "fair" flags, store them as boolean columns in the table.
I would recommend against Clodoaldo's solution unless the space is REALLY hard - see this question.
It looks like the columns you mention are "business critical", meaning there might not be enough to store the "indexed" columns, but also the date the index was indexed. It may be necessary to restrict combinations of states or impose rules for sequencing (you cannot go to completion while in an error state). In this case, you can implement the "item_status" table to store history, etc.
In this case, your schema will look 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 last date string for this item.
source to share