Skip to main content
Database Administrators

You are not logged in. Your edit will be placed in a queue until it is peer reviewed.

We welcome edits that make the post easier to understand and more valuable for readers. Because community members review edits, please try to make the post substantially better than how you found it, for example, by fixing grammar or adding additional resources and hyperlinks.

Required fields*

4
  • 30
    I wouldn't do it this way. It looks elegant, but it's a horrible pattern to actually use. You need to reference a data dictionary to determine which bit is which, it's nearly impossible to tell at a glance what is going on with the data, it usually can't be meaningfully indexed, adding columns is difficult, removing columns is very difficult, bitmask operations are easy to get the logic backwards (flags & '1010' = flags vs flags & '1010' = '1010'), etc. Commented Jun 11, 2021 at 15:10
  • 2
    @BaconBits Sure it is ugly, but not much uglier than an array and a JSON. But I can accept that opinions differ on that. Commented Jun 11, 2021 at 20:08
  • @BaconBits do you have a better solution? Commented Jun 28, 2024 at 19:51
  • 1
    @CardinalSystem The best alternative is creating columns that are single bits or Boolean values. The best idea is don't break first normal form. Most RDBMSs will bitpack them for you so it's not a storage problem. If you really need an open schema then use an Entity Attribute Value table (OP's third design). EAV tables do suck, but they work. A JSON column is probably 3rd best to me. And I'd say an array is equally as bad as a bitmask if you need to care about array element order because each element represents a different entity. A comma delimited string would be even worse! Commented Jul 5, 2024 at 12:47