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.

4
  • 3
    I like this approach as well. It works well enough until you have to filter on multiple flags at once, which is rather cumbersome. Might not be an issue in the OP's specific scenario (one flag per user, as I understood) but is still worth mentioning as a serious enough drawback in general, to give the reader a balanced view on this approach. Commented Jun 12, 2021 at 7:01
  • Agreed that of the three that is the most "normal" form for an RDBMS. The one issue with it as written is that is relies on a flag column that is actually three states - Truthy, Falsy and non-existent row. @Polygorial's answer addresses this and to the point of the OP's question is very much a common (perhaps even canonical) pattern for this data. Commented Jun 13, 2021 at 16:17
  • @640KB Could you elaborate on how that three states is an issue though? (I'm sure in some use cases it can be, but don't have any off the top of my head since in my prior use cases it wasn't.) Commented Jun 13, 2021 at 17:00
  • For one, there are logically only two states (boolean), so design-wise that's already a mismatch. Second the application that's utilizing the data would need to be built to handle two cases for the same value (if Falsy or if null). Third if you wanted to toggle the flag you'd have to do multiple operations (1: check if row exists, 2: if not, do an insert, otherwise do an update). The alternative to that would be to need to always insert 100 or so default rows when a user is created and insert a corresponding row to each user when a new flag is added to ensure there's always a value. Commented Jun 13, 2021 at 17:08