I have a settings table.

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.

  • For changing user settings efficiency is unlikely to matter. Do whatever is easiest to code.
    – Andomar
    Sep 28, 2012 at 14:56

2 Answers 2


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.

  • Excellent design suggestions. Thank you.
    – dangowans
    Sep 28, 2012 at 20:15

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.