For a boolean settings table, should I toggle a flag, or insert/delete?

Question

I have a settings table.

SETTINGS
--------
settingId int
settingName varchar(20)

Each user can choose to apply the settings, or not. The interface allows them to quickly toggle the settings on or off. The setting is by default off.

I'm looking for best practice on the USER_SETTINGS table.

Should it be designed such that only active settings are included in the USER_SETTINGS table? This would involve a lot of inserts and deletes on the table.

USER_SETTINGS (only active settings)
------------------------------------
settingId int
userId int

Or, should it be designed with an active flag? This would involve a single insert for each setting, followed by several updates.

USER_SETTINGS (settings enforced by a flag)
-------------------------------------------
settingId int
userId int
active bit

Setting history is not really important to me, so I wouldn't need to see if the active flag was ever used.

Is it best to use a smaller table and insert, delete, insert, delete, etc., or is it best to add that extra bit, and insert, update, update, update?

I'm running SQL Server 2005.

Answer 1

Let me start by saying both strategies are fine. The volume of user settings changes is likely going to be low enough that you should focus on more critical areas. So there is a good argument for "do what's easiest for you" (aka @Andomar). That could come down to how your UI is laid out, behavior or your ORM, integration of another system, whatever. Both are fine designs.

My Design
With that said, I would design the setup as follows:

A Settings table that defines the available settings, including a column for a recommended default value. In UserSettings table I would keep a complete list of the settings for each user. I'm not sure I would use an active column, so much as use NULL to indicate inactive (although this is a minor choice in my opinion).

In addition to assuming a complete set of settings existed, I would write my Queries/Procs to assume I could stumble upon a missing setting. If I were to stumble, so to speak, the logic would add the missing setting.

So why would I design it this way?

• The basic structure of your two tables is fine, so no need to alter it much
• Insertions and deletions are expensive relative to updating a bit or a setting choice
• Keeping a stable table structure will reduce index fragmentation & clustered index re-jiggering. Granted this won't be much of an issue in a low volume DB with a maintenance plan. In a higher volume DB, however, index fragmentation can become expensive & a performance hog.
• I added the bit about "missing then add" logic to account for settings being added in the future. This way you can roll out a new setting and users data will self-healed in a way.

Hope that helps.

Answer 2

active flag seems redundant to me - I don't see any value in storing it. Don't worry too much about delete/insert performance, I cannot think why user would change his options frequently. Even if some application controls user settings, I believe it's still relatively rare operation. Also, if on application side you use one or other ORM library, it's usually easier to map many-to-many relationship if link table has no extra columns.