Table Design For SystemSettings, Best Model

database-design, denormalization, normalization, sql

Solution

This is a variation of an "Entity Attribute Value" schema (Joel and random SO question)

It has a few pros and more cons, and it pretty much guaranteed to end in tears.

Problem

Someone suggested moving a table full of settings, where each column is a setting name(or type) and the rows are the customers & their respective settings for each setting. ID | IsAdmin | ImagePath ------------------------------ 12 | 1 | \path\to\images 34 | 0 | \path\to\images The downside to this is every time we want a new setting name(or type) we alter the table(via sql) and add the new (column)setting name/type. Then update the rows(so that each customer now has a value for that setting). The new table design proposal. The proposal is to have a column for setting name and another column for setting. ID | SettingName | SettingValue ---------------------------- 12 | IsAdmin | 1 12 | ImagePath | \path\to\images 34 | IsAdmin | 0 34 | ImagePath | \path\to\images The point they made was that adding a new setting was as easy as a simple insert statement to the row, no added column. But something doesn't feel right about the second design, it looks bad, but I can't come up with any arguments against it. Am I wrong?

Original source

Related problems