I cannot count the times, which using a flag (also, called an indicator) is described as a nice to have in database table design, at least, until the code runs into complexity and/or performance challenges.
When designing your data models, ETL’s, and reports it is useful to consider how indicator flags can help. While indicator flags are, normally, binary in nature, such as True/False or Yes/No, but indicator flags don’t always need to be binary.
How indicator flags can help your processes and reporting:
- Provide an equijoin for complex business rules, which can otherwise result ‘Not In List’, ‘In list’, ‘Not Exists., ‘Exists’, ‘Not Equal To’ and sub-selects SQL statements
- Provide processing maker to prevent look ups to other tables to determine an attribute. For example, a snapshot type (daily, weekly, monthly) flag, which can be used to apply data retention rules.
- Provide a mark for a special circumstance. For example, a Legal Hold flag to mark record to be exempted from removal and/or change to meet legal proceeding requirements.
- The judicious planning and use of flags can reduce the number of full table scans required against large tables.