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.