Storing settings inside DB

Question

Hello,

can someone tell me what's the best way to store many different settings in a database? Should I use one table with id,key,value?

Here is an example what i have to store:

PageNavigation.Font = "fontRegular";
PageNavigation.FontSize = 14;
PageNavigation.FontColor = 0x000000;
PageNavigation.SubFont = "fontRegular";
PageNavigation.SubFontSize = 12;
PageNavigation.SubFontColor = 0x000000;
PageNavigation.width = 200;
PageNavigation.height = 22;
PageNavigation.spacing = 5;
PageNavigation.LetterSpacing = 0;
PageNavigation.top = 250;
PageNavigation.rightMargin = 24;
PageNavigation.RollOverColor = 0xFF3300;
PageNavigation.ActiveColor = 0xCCCCCC;
PageNavigation.Icon = "/assets/images/arrow_default.png";
PageNavigation.IconLeft = 5;
PageNavigation.TextLeft = 5;
PageNavigation.SubIcon = "";
PageNavigation.SubIconLeft = 5;
PageNavigation.SubTextLeft = 22;

PageViewer.BackgroundColor = 0xe9edee;
PageViewer.ThumbSource = "";
PageViewer.maxVisible = 17;
PageViewer.ThumbWidth = 38;
PageViewer.ThumbHeight = 49;
PageViewer.ThumbActiveBorder = 2;
PageViewer.ThumbActiveBorderColor = 0xEE2233;
PageViewer.ThumbSpacing = 10;
PageViewer.ThumbLeft = 20;
PageViewer.ThumbBorderColor = 0xFF3300;
PageViewer.ThumbBorderSize = 1;
PageViewer.ThumbRollOverColor = 0xDDDDDD;
PageViewer.ThumbActiveColor = 0xCCCCCC;
PageViewer.ThumbSelectColor = 0xCCCCCC;
PageViewer.ThumbShadow = 1;
PageViewer.ThumbLayout = "Layout1";
PageViewer.ButtonLayout = "ButtonLayout1";

I'm new to database design and don't know good resources to learn db-design

Cheers, Dom

Answer 1

the best I think is to create a field for each type of setting. What does this do is,

• we can keep many user defined settings of many users in one table.
• we can create different sets of predefined setting which can be useful in several cases

ETC

Answer 2

Yes this would work but has the downside that settings values has no proper type. You would have to declare everything as VARCHAR, your application would have to check the validity of the values.

Sure it would be possible to create a table for each setting type, e.g.

create table Settings
(
  settings_id primary key int not null,
  name VARCHAR(255) not null,
);

create table DoubleValues
(
  value double not null
  fk_setting int not null;
);

create table ColorValues
(
  value char(8) not null
  fk_setting int not null;
);

..

Another way would be to create a table consisting of one row with a column for every setting. This way you have proper typing and can add check constraints at database level. But I consider this one as worse to maintain and to extend, so I would prefer the first one, but as always, it depends on your specific needs.

Another way would be to use a simple text file and read the settings into a hashtable which will be by far the fastest way. Maybe you can have a look at a noSQL database like MongoDb or CouchDb.

Think if your settings are mostly static or how often new settings keys are inserted or removed.