CODEWITHSUNDEEP
sqlsql-server-2008database-design
tmesser
tmesser

Reputation: 7656

User settings in SQL Server

I am trying to design an efficient database schema for user settings in SQL Server 2008 R2. The wrinkle here is that we need multiple levels of granularity, and I'm not sure how to efficiently represent that.

We have a handful of settings that can be applied to a full Account, a single Module, or a specific Feature. Currently the way the table has been set up is something to the effect of:

AccountId int
ModuleId int
FeatureId int
SettingData string

(please don't get hung up on what SettingData is or isn't, I just made it a string here in the example to distinguish it from the other Ids).

Problem: Many customers have access to many modules, and these modules have access to many features. A single Account making a change to SettingData can modify 4000 records. This is absolutely not tenable for obvious reasons, and I'm determined to fix it.

The solution is obviously to have a few different tables that, by their usage, override eachother and allow some account wide settings and granular preferences. However, I've never done this before and my attempts at designing it end up looking disturbingly similar to the inefficient table structure we currently have.

Thanks in advance, any help is appreciated.

Upvotes: 1

Views: 886

Answers (1)

user359040
user359040

Reputation:

It sounds as though settings can currently be specified at the following levels:

  • Account
  • Module
  • Feature

Given that there are probably already tables set up for each of Account, Module and Feature, it would appear to make sense to:

  • Remove the existing table.
  • Set up a new field for setting data on each of the existing Account, Module and Feature tables.

Since the general principle is that the specific should override the general, a Module-level setting should override an Account-level setting, and a Feature-level setting should override a Module-level setting.

The advantage of this approach is that any time a specific setting was updated, only a single record would need to be updated.

The disadvantage is that to determine which setting should apply to a specific feature (for a specific account) in a specific module, 3 tables would have to be queried instead of one.

Upvotes: 1

Related Questions