CODEWITHSUNDEEP
mysqlxmljsondatabase-designsetting
abksharma
abksharma

Reputation: 598

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

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

  1. 123,AssignedQuiz,Science
  2. 123,AssignedQuiz,Maths
  3. 456,AssignedQuiz,Geo

Upvotes: 2

Views: 1093

Answers (2)

abksharma
abksharma

Reputation: 598

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)

Upvotes: 0

Suresh Kamrushi
Suresh Kamrushi

Reputation: 16086

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)

Upvotes: 1

Related Questions