Design for storing data only applicable to a small subset of records

Question

So my boss and I are in disagreement how to best design our database for storing miscellaneous data for certain clients.

Basically, we have a bunch of tables in some format similar to this (in pseudo-code for simplicity):

TABLE [dbo].[SalesData]
[CampaignID] int IDENTITY(1, 1) NOT NULL, (This is the PK)
[CustomerID] int NULL,
[Calls] float NULL,
[Responses] float NULL,
[Sales] float NULL,
[Revenue] money NULL,
[Cost] money NULL,
[WebSales] float NULL,
[Channel] nvarchar(20) NULL

What happens is as we gain clients, we are sometimes forced to add in some kind of custom data collecting for each one. For this table, everything is used by every client (we have about 20) except for WebSales and Channel, which is only used for 1 client. Another client wants a couple new pieces of data tracked, and therefore, we either need to add more columns, or spin off that data into a related table and start a supertype-subtype system.

What I want to do is also spin off WebSales and Channel, and add a table for each customer in this manner:

TABLE [dbo].[SalesData_Client1] 
[CampaignID] int NOT NULL, (PK and FK to the SalesData table)
[WebSales] float NOT NULL DEFAULT 0,
[Channel] nvarchar(20) NOT NULL DEFAULT ''
ADD CONSTRAINT [CK__SalesData_Client1__CustomersCampaignID] 
CHECK (dbo.CustomerOfCampaignID(CampaignID) = Client1) 

Then I would drop those columns from the original table and make the second client a table like this as well. The boss thinks this means we'll have to add too many tables as we gain clients, and wants to add more columns. He would rather change the SalesData table like this:

TABLE [dbo].[SalesData]
[CampaignID] int IDENTITY(1, 1) NOT NULL, (This is the PK)
[CustomerID] int NULL,
[Calls] float NULL,
[Responses] float NULL,
[Sales] float NULL,
[Revenue] money NULL,
[Cost] money NULL,
[Unknown] char(10) NULL,
[Misc1] float NULL,
[Misc2] nvarchar(20) NULL
[Misc3] int NULL,
[Misc4] int NULL
[Misc5] int NULL

Where the "Misc" columns can be used for whatever, and then the programmers can "just figure it out based on the client". I know the reasons as to why you shouldn't do it this way, it violates normalization and will make performance worse and worse as we add columns, etc. But he keeps coming back with "It's way simpler" and doesn't like that every new client could mean another new table. He also makes the point that newer columns like Websales might be used by more than 1 client eventually, and it will be a huge pain to have it in multiple subtype tables, or to move it from multiple subtype tables to the main table if we should ever want to do that.

Is there a better method than supertype/subtype solution that I came up with that can make things easier for him and the programmers? An important note is that each client's data is basically in it's own universe and will NEVER mix. I realize it's kind of subjective and the answer will depend on how many "Misc" columns we are seriously expecting. The reason I want to go the subtype route is because the next client could require 5 new bigint columns for all I know.

Answer 1

To be truly flexible, you could go the Entity-Attribute-Value route, but that would probably be overkill for this and there are many who would argue that EAV is a terrible anti-pattern.

I think the client-specific tables might be the better way to go if you think you will eventually end up with dozens and dozens of client-specific columns in a single table.

Answer 2

This probably isn't the right option in your case, but I'll throw it out there anyway. You could have a second table with a CampaignID PK, and all the various customer-specific fields. It could end up very wide, but you'd keep type safety and sensible names, and your core table would stay clean. Because you'd only store a row for customers with any custom fields, storage should be reasonable.

CREATE TABLE CustomSalesData
(
CampaignID       INT NOT NULL PRIMARY KEY REFERENCES SalesData (CampaignID),
WebSales         DECIMAL(18, 2),
Channel          NVARCHAR(20),
BobsCustomField  INT,
FredsCustomField VARCHAR(100)
)

You'd end up with sparse columns, but there are some tools to keep such tables fast and compact. You may choose to prefix the custom fields with the customers' names, decreasing ambiguity but preventing you from reusing a field for multiple customers.

This is similar to the data warehouse concept of a "junk dimension," which holds all the miscellaneous bits of data which you don't want cluttering up your fact table but which don't rate having dimension tables of their own.

To be clear: I'm not really recommending this option in your case; any solution which entails DDL on a routine basis is, IMHO, not a solution. I recommend the EAV pattern which Frustrated provided.