Database design: multiple "options" tables vs. a single table

Question

I believe all of us seen this design requirement in one form or another.

Software needs to offer a user several answer options for various questions/settings. These options reside in the database. In a big project a number of such questions can easily surpass 50.

Now.

Is it okay to have 50+ tables with the identical design like this?

CREATE TABLE [dbo].[Setting1]
(
    [ID] uniqueidentifier NOT NULL,
    [Name] varchar(max) NOT NULL
)

CREATE TABLE [dbo].[Setting2]
(
    [ID] uniqueidentifier NOT NULL,
    [Name] varchar(max) NOT NULL
)

etc.

and then have these tables linked via foreign key like this:

CREATE TABLE [dbo].[UserSettings]
(
    [UserID] uniqueidentifier NOT NULL,
    [Setting1ID] uniqueidentifier NOT NULL,
    [Setting2ID] uniqueidentifier NOT NULL
)

Or is it more sensible to create one "master" options table?

CREATE TABLE [dbo].[Setting]
(
    [ID] uniqueidentifier NOT NULL,
    [Name] varchar(max) NOT NULL
    [SettingCode] int NOT NULL
)

What are advantages and disadvantages besides having to multiply tables with similar structure in the first cases and having no integrity constraints in the other?

Answer 1

Questions like this are always going to have subjective answers, but personally with that many individual settings I'd probably do something lateral like store them in an XML file and persist that to the DB linked against the user (using a BLOB type field so you don't bump into any varchar length limit).

That way you can load it up, de-serialise it into a configuration object and have nice programmatic access to the settings without having to write tons of SQL or hit the DB every time you want to check something.

Answer 2

I'd go with the last option, the lookup table, but a slightly different naming scheme...

"Setting" is where you store the various names/descriptions of the settings, with a primary key for each. Then, tie the users to settings in another table. Unlimited settings, unlimited user/setting relationships.

Setting

SettingID - primary key
SettingName

UserSetting

UserID
SettingID

Answer 3

Mmh, I think that creating X tables for X settings is a very bad solution.

I like to solve this issue this way:

Users(UserID, ...)
Settings(SettingID, SettingFieldName, ...)
UserSettings(UserdID, SettingID, Value)

But I don't know if this is a theoretically a right solution. I proceed this way, usually, and I think it's good.