Sql-server – Storing multiple settings for multiple users in a SQL Server table

sql serversql-server-2012

I'm using SQL Server 2012 to store the account settings for multiple accounts (each user account will have a set of settings). These settings could have any number of fields (has not been completely set and could grow) but each user account will have the same set of settings (different values of course).

Is it better (faster with a smaller footprint and more efficient) to:

  1. give each setting its own column and each row to a user? keep in mind that there could be hundreds of settings variables. (i.e. userid, setting1, setting2, setting3…etc)

  2. Relate each individual setting to a user and making it a name value pair table
    (i.e. userid, settingsName, settingValue)

Best Answer

Already maintainability will ask for a structure like shown below.
An additional set of templates would support fast capture of new data (Structure like UserSettings with a templateid instead of userid)

CREATE TABLE [dbo].[Settings](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](50) NOT NULL,
    [ValueType] [int] NOT NULL,
 CONSTRAINT [PK_Settings] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
CREATE TABLE [dbo].[UserSettings](
    [userid] [int] NOT NULL,
    [settingsid] [int] NOT NULL,
    [value] [sql_variant] NULL,
 CONSTRAINT [PK_UserSettings] PRIMARY KEY CLUSTERED 
(
    [userid] ASC,
    [settingsid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
Related Question