Mysql – Best way to handle column that doesn’t conform to one particular type

database-designMySQL

I have a Settings table:

CREATE TABLE `Settings` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `Code` varchar(100) NOT NULL,
  `Name` varchar(250) NOT NULL,
  `Description` text NOT NULL,
  `Default` varchar(30) NOT NULL, <<<<<<<
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

The Default value may however be integers, booleans, text, enums etc. etc.
I have set it to varchar right now, and when retrieving the setting, I will know what setting it is, e.g Send me daily emails – True/False, and do a case at the application level given that I know the type the value should be for that particular setting.

Is there a better way of doing this?

Best Answer

The approach you are proposing is workable but informal. A more formal approach can be found in my answer to this question.

The basic pattern is called a Property Bag.

Related Question