Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)

share|improve this question
    
You are using SQL Server, not SQL. Drives me mad when people call it just SQL! –  Phil Mar 9 '13 at 23:43
    
Though I like @bummi 's answer, You should always test it yourself, as performance differs from one system to another (due to different data, number of CPUs and parallelism, indexes, etc.) –  Justicator Mar 10 '13 at 8:48
    
@Phil Sorry about that I didn't even realize that I had done that :) –  hjavaher Mar 11 '13 at 0:46
add comment

1 Answer 1

up vote 1 down vote accepted

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]
share|improve this answer
    
Thank you, Makes a lot of sense. I think that's what I'll do. –  hjavaher Mar 11 '13 at 0:47
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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