How to Store Key-Value or One to Many User Settings in database (Xml, Json)?

Question

Should we Keep application key-value settings data in Serialized manner in db like XML or JSON format. e.g. user setting data can be kept in serialized manner in a single nvarchar(2000) column

Or I should keep them as table with columns like BELOW

User ID, Setting Name, Setting Value

123,AssignedQuiz,Science
123,AssignedQuiz,Maths
456,AssignedQuiz,Geo

I think A better table structure will be better than storing in serialized manner. — Suresh Kamrushi, Jan 15 '14 at 08:47

score 1 · Accepted Answer · answered Jan 15 '14 at 08:50

1

While storing in serialized manner it is difficult to update a single value in it. Instead You need to update the column every time.
And if you store in table base it easy to update a single row or retrieve it from table.

You can define structure as:
Table:setting
column(settingid,setting_name,setting_value)

Table: user_settings
column(userid,settingid)

answered Jan 15 '14 at 08:50

Suresh Kamrushi

15,627
13
75
90

but ms sql server supports XML data type and indexing and querying on it, also xpath updates, selects so How about it...? – abksharma Jan 15 '14 at 08:55
As per your requirement list out all pros and cons and than decide on that. It is difficult here to decide as I don't know what you have and you don't. – Suresh Kamrushi Jan 15 '14 at 08:58
marking this as an answer...will ask other question (when do we use database XML data type..???) in another thread. – abksharma Jan 15 '14 at 09:01
Than U suresh! but One last question...will this structure work fine for **10 million users*100 Settings**?? – abksharma Jan 17 '14 at 06:32
2

I agree with Surersh.. Using table approach reduces workload. – Daryl Jan 17 '14 at 06:46
1

@abksharma: yes definitely, this structure will work fine with more 10M user and their settings. – Suresh Kamrushi Jan 17 '14 at 07:14

abksharma · Answer 2 · 2014-01-18T05:53:52.907

FINAL STRUCTURE I had But will this structure be scalable upto 10 million users*100 Settings = 1 Billion settings Mysql?? YES IT WILL!!!

SettingType TABLE

   SettingTypeId    UNSIGNED SMALLINT(2)
    SettingName VARCHAR(100)
    SettingDescription  VARCHAR(300)
    Created DATETIME
    LastUpdated TIMESTAMP
    CreatedBy   UNSIGNED INT(4)
    ModifiedBy  UNSIGNED INT(4)
    Deleted BIT
    Disabled    BIT
    RecordVersion   UNSIGNED INT(4)

Final structure:-

Setting TABLE

   SettingId    UNSIGNED INT(4)
    SettingTypeId   UNSIGNED SMALLINT(2)
    UserId  UNSIGNED INT(4)
    SettingValue    VARCHAR(1000)
    Created DATETIME
    LastUpdated TIMESTAMP
    CreatedBy   UNSIGNED INT(4)
    ModifiedBy  UNSIGNED INT(4)
    Deleted BIT
    Disabled    BIT
    RecordVersion   UNSIGNED INT(4)

How to Store Key-Value or One to Many User Settings in database (Xml, Json)?

2 Answers2