For application, which has few (5-10) settings at the moment but will have a lot more (up to 100) in the future which would be better approach:
- to add new columns to settings table every time new setting is introduced
- to have settings table as key/value storage
Consider that application might have millions of instances (which all run on the same db and use the same table for settings), DB might be sharded. DB is relational, i.e. either MySQL or T-SQL.
As a developer I would prefer the 2nd variant, so I could scale the application without changing the DB schema and add/remove settings at will. As far as I understand having several million records in a single table shouldn't be an issue if the index is clustered on application instance. Are there any drawbacks that I'm not aware of?
And what about 1st variant? Are there any big benefits? And what about the number of columns: is there any theoretical limit of columns that table can have? What happened if I had a table with 1000 (10 000, 1000 000) columns? Would that be slow?