Storing User Settings - anything wrong with using "Flags" or "Bits" instead of a bunch of bools?

Question

I'm designing the User Settings for my MVC application, and right now I have ~20 boolean settings that the user can toggle. Since every user will always have every setting, I was thinking about storing each setting as a boolean on the User table. Though this would become unwieldy as the application requirements grow.

First question - is there anything wrong with having a ton of columns on your table in this situation?

I then considered using Flags, and storing the settings as one bit each in an array:

[Flags]
public enum Settings
{
    WantsEmail = 1,
    WantsNotifications = 2,
    SharesProfile = 4,
    EatsLasagna = 8
}

And then each user will have a single "Settings" column in their User row, that stores a value of 2^20, if there are 20 settings.

I'd use this to guide my efforts: What does the [Flags] Enum Attribute mean in C#?

Is this better than the former approach? Any suggestions are welcome.

Answer 1

It depends on what should be considered atomic from the data management perspective.

• If you always search for, read and write all the settings together from/to the database, then the whole set of settings can be considered atomic and can be stored together in the database.
• However, if you need to do any of those things on a subset of settings (e.g. set just one flag without modifying others), then they are not atomic (from the data management perspective), so storing them together in the same database field would violate the principle of atomicity and therefore the 1NF.

Please note that some DBMSes (such as MS SQL Server) are very efficient at storing Booleans (just one bit per Boolean field under ideal circumstances). Even those that are less than perfect will typically not spend more than one byte per Boolean (Oracle, I'm looking at you), which may become a problem only if you have millions or billions of users.

Answer 2

If you have only 20 values and there is no chance some day it will be 100 values - it is ok to use any of these (preferably the enum). But if there is a chance to get 100 values - you really need to build a key-value pairs table (there is a table Users, table Settings and table UsersSettings that maps one to another).