How to handle a few dozen flags in a database

Question

Like most apps, mine has a "users" table that describes the entities that can log in to it. It has info like their alias, their email address, their salted password hashes, and all the usual candidates.

However, as my app has grown, I've needed more and more special case "flags" that I've generally just stuck in the users table. Stuff like whether their most recent monthly email has been transmitted yet, whether they've dismissed the tutorial popup, how many times they clicked the "I am awesome" button, etc.

I am beginning to have quite a few of these fields, and the majority of these flags I don't need for the majority of the webpages that I handle.

Is there anything wrong with keeping all of these flags in the users table? Is there somewhere better to put them? Would creating other tables with a 1:1 relationship with the users table provide additional overhead to retrieving the data when I do need it?

Also, I use Hibernate as my ORM, and I worry that creating a bunch of extra tables for this information means that I'd also have to dirty up my User domain object. Advice?

Answer 1

There are several common solutions:

• EAV

  Store one flag per row in a child table, with a reference to the user row, the name of the flag, and the value. Disadvantages: Can't guarantee a row exists for each flag. Need to define another lookup table for flag names. Reconstituting a User record with all its flags is a very costly query (requires a join per flag).

• Bit field

  Store one flag per bit in a single long binary column. Use bitmasking in application code to interpret the flags. Disadvantages: Artificial limit on number of flags. Hard to drop a flag when it becomes obsolete. Harder to change flag values, search for specific flag values, or aggregate based on flag values without resorting to confusing bitwise operators.

• Normalized design

  Store one BIT column per flag, all in the Users table. Most "correct" design from the perspective of relational theory and normalization. Disadvantages: Adding a flag requires ALTER TABLE ADD COLUMN. Also, you might exceed the number of columns or row size supported by your brand of RDBMS.