Our app has been customized to handle many different types of customers, with certain settings that will only apply to a few or one customer. Rather than continuously adding nullable columns to the customers table, I decided to add a [Settings] table to allow each setting to be a row.
[dbo].[Settings]
[SettingID] [int]
[SettingCode] [nchar](4)
[SettingDescription] [nvarchar](255)
Which is then linked to the [Customers] table through a many-to-many table
[dbo].[Customer_Settings]
[Customer_SettingsID] [int]
[CustomerID] [int]
[SettingID] [int]
My question is about how to handle the fact that many of these settings need an additional data type on the [Customer_Settings] table.
For example, we could have one setting be "Latest Delivery Time" requiring a time datatype, or another be "Minutes Until Expiration" requiring an int.
The two ways I can think of to handle this is to add nullable columns to the [Customer_Settings] table like:
[dbo].[Customer_Settings]
[Customer_SettingsID] [int]
[CustomerID] [int]
[SettingID] [int]
[ValueTime] [time] NULL
[ValueInt] [int] NULL
...
This seems like bad design.
The other way I can think of is to add child tables to [Customer_Settings] table like:
[dbo].[Customer_Settings_Int]
[Customer_Settings_Int_ID] [int]
[Customer_SettingsID] [int]
[Value] [int]
This seems like it is normalized but also cumbersome. Please let me know if one of these is clearly better or if there is another alternative. Thanks!
The solution you have chosen is called Entity-Attribute-Value (EAV.) The most common approach is to store all values as strings. Some people add a validator column, containing a regex or like expression, that is verified by the client or t-sql function updating the value.
It is much much cleaner to use nullable columns.