Storing account settings in a single row with complex data

Question

I need to store account settings for each account profile. I decided to use SQL DB for this, but not sure should I go with complex data (json/xml).

I found answers

Using a Single Row configuration table in SQL Server database. Bad idea?

https://softwareengineering.stackexchange.com/questions/163606/configuration-data-single-row-table-vs-name-value-pair-table

but none of them discusses using single row approach containing complex data

Complex data would be stored inside a following DB table

AccountID int
AccountSettings nvarchar(max)

and would contain AccountSettings data such as

"settings": {
  "branding": {
    "header_color": "1A00C3",
    "page_background_color": "333333",
    "tab_background_color": "3915A2",
    "text_color": "FFFFFF",
    "header_logo_url": "/path/to/header_logo.png",
    "favicon_url": "/path/to/favicon.png",
  },
  "apps": {
    "use":            true,
    "create_private": false,
    "create_public":  true
  },
  "tickets": {
    "comments_public_by_default":     true,
    "list_newest_comments_first":     true,
    "collaboration":                  true,
    "private_attachments":            true,
    "agent_collision":                true
    "list_empty_views":               true,
    "maximum_personal_views_to_list": 12,
    "tagging":                        true,
    "markdown_ticket_comments":       false
  },
  "chat": {
    "maximum_request_count": 5,
    "welcome_message":       "Hello, how may I help you?",
    "enabled":               true
  },
  "voice": {
    "enabled":     true,
    "maintenance": false,
    "logging":     true
  },
  "twitter": {
    "shorten_url": "optional"
  },
  "users": {
    "tagging": true,
    "time_zone_selection": true,
    "language_selection": true
  },
  "billing": {
    "backend": 'internal'
  },
  "brands": {
    "default_brand_id": 47
  },
  "active_features": {
    "on_hold_status":                true,
    "user_tagging":                  true,
    "ticket_tagging":                true,
    "topic_suggestion":              true,
    "voice":                         true,
    "business_hours":                true,
    "facebook_login":                true,
    "google_login":                  true,
    "twitter_login":                 true,
    "forum_analytics":               true,
    "agent_forwarding":              true,
    "chat":                          true,
    "chat_about_my_ticket":          true,
    "customer_satisfaction":         true,
    "csat_reason_code":              true,
    "screencasts":                   true,
    "markdown":                      true,
    "language_detection":            true,
    "bcc_archiving":                 true,
    "allow_ccs":                     true,
    "advanced_analytics":            true,
    "sandbox":                       true,
    "suspended_ticket_notification": true,
    "twitter":                       true,
    "facebook":                      true,
    "feedback_tabs":                 true,
    "dynamic_contents":              true,
    "light_agents":                  true
  },
  "ticket_sharing_partners": [
    "foo@example.com"
  ]
}

Other solution is a widely used single row approach such as AccountID int SettingsName nvarchar(max) SettingsValue nvarchar(max)

that could hold data such as

AccountID     SettingsName                   SettingsValue
1             Branding.Header_Color          1A00C3
1             Branding.Page_Background_Color 333333
1             Apps.Use                       true

......

I assume both solutions are valid and would depend based on application needs, but would REALLY like to know is there an issue that i am not seeing when using complex data with single row approach to store application settings?

score 0 · Answer 1 · answered Feb 27 '16 at 21:11

One concern is if you have a busy large database, you cannot do ONLINE re-indexing (if you have the Enterprise version) on XML, text, varchar(max) type fields. This causes grief for 2008 R2. The newer versions of MS SQL Server can re-index online varchar(max) fields, so it depends on which version you are running.

Also, you won't be able to query or index if you need to search for specific records, unless you go with the SettingsName, SettingsValue type of table, which I've used a lot. This solves the re-index online issue (if that applies to your situation), as well as indexing the fields for quick queries.

Storing account settings in a single row with complex data

1 Answers1