How to Store Multiple Item States Efficiently Using Bitmasking in Databases

Problem Overview

In application development a single record often needs to hold multiple status flags, for example a product that is hot, new, and on sale. The article discusses how to design a database to support such multi‑status scenarios.

Common Approaches

1. Association Table

Create a linking table with fields: relation_id, product_id, attribute_id. Queries join this table to find products with a specific attribute. The application writes to both the product table and the association table.

2. Delimited String

Store all attributes in a single column separated by a delimiter (e.g., 01|02|03 for hot, new, sale). Queries use LIKE patterns. The program must split the string for reading and concatenate values for writing.

Both methods increase schema and code complexity and are hard to extend.

Bitmask Solution

Use a binary bitmask where each bit represents a status. Store the bitmask as an integer.

Example mapping:

Hot    : 0000 0001 New    : 0000 0010 Sale   : 0000 0100 Combined states are obtained by bitwise OR:

Hot + New  : 0000 0011 Hot + Sale : 0000 0101 Hot + New + Sale: 0000 0111 The binary value is stored as an integer in the product table.

Querying the Bitmask (Oracle Example)

Use the BITAND function to test a flag: SELECT * FROM T_PRODUCT WHERE BITAND(property, 1) = 1 Here 1 corresponds to the “Hot” flag.

Handling Bitmask in Java

Check inclusion with the & operator, e.g., (flags & 1) == 1.

Combine flags with the | operator, e.g., 1 | 2 = 3.

Additional flags can be added later, such as a “Clearance” flag 0000 1000.

Advantages and Disadvantages

Benefits

Compact storage

Strong extensibility

Drawbacks

Higher learning curve for developers

Bitmask ↔ Decimal Reference Table

The article includes a table (image) that maps four‑status combinations between binary and decimal representations.