Storing extendable options list in MySQL table? Best practice?

Question

Hello,

Let's suppose that we have multi-site CMS and every website in this CMS having 2 options: preview_width and preview_height. We know that every option should have default value if isn't defined by user. Also we know that list of options will be extented in near future. What is the best practice to store such options in MySQL table?

I know three practices and both of them have lacks (or maybe I don't know how to correctly use this practices)...

1. Practice #1: Each option is represented as column in options table.

   Disadvantage: We should modify options table each time we're adding new option.

2. Practice #2: All options are stored as serialized object/array in options column of sites table.

   Disadvantages: To add new option with default value - we need to loop through all rows and modify serialized options; or we can add this option when it is requested and found not present.

3. Practice #3: All options are stored in options table with structure: id, site_id, option_name, option_value.

   Disadvantages: When adding new option we should update this table with default-valued options for each website.

What is your choice? What practice to choose when new options are added very often? Any other practices?

Thank you.

Answer 1

I would use Practice #3. In order to store default value you can try writing a method to get options:

get_value(option) {
  value = read_from_db(option);
  if value == not_present_in_db {
    value = default_value(option);
  }
  return value;
}

You also need to write default_value(option) method which should look for some defaults in a configuration file or whatever.