Discuss application settings table design

Application settings table has one row with over 220 columns and growing.

Another design could be storing each setting in a single row, with a key, and value column.

Current Design - one row with many columns

Advantages

• Can have columns of different data types
• Is easy to create a rails admin form for with rails helpers
• We can currently use plain Rails validations for individual settings, we'd need to implement this ourselves if we wanted to change strategies.

Disadvantages

• Growing column size
• Adding a new setting requires DB review.
• Adding a new setting requires exclusive lock on the table. (Long running transaction might query the table and could lead to failed migration).

application_settings - many rows with key/value columns

Advantages

• Doesn't require DB review
• Flexibility - developers can easily add new settings
• Query just the settings you need - although it's not a lot of data (I don't think) currently to get ANY setting you get ALL settings

Disadvantages

• How to handle different types of settings ( a jsonb or similar construct )

Current Design

Column Types

> ApplicationSetting.columns.map {|c| c.sql_type}.uniq
=> ["integer",
 "boolean",
 "text",
 "timestamp without time zone",
 "character varying",
 "bigint",
 "numeric",
 "double precision",
 "character varying(255)",
 "character varying(511)",
 "timestamp with time zone",
 "character varying(128)",
 "date",
 "smallint"]