I have a table that contains customer info. Things like Phone number, address, age, and several different custom groupings. All of these things can change on any given day for any given customer. I have been asked to create something that would show a running history for each guest. Such that we could say on Day X Customer A had a Phone number of that, an address of that, age of that and so on. However on Day Y Customer had a Phone number of this, an address of this, age of this and so on.
Below is a sample of some field being monitored by this.(Did not want to list all several hundred of them)
Create Table CustHist
(
CustID int
,Phone Varchar(21)
,address1a varchar(30)
,address1b varchar(30)
,city1 varchar(30)
,State1 Varchar(30)
,zip1 varchar(5)
,address2a varchar(30)
,address2b varchar(30)
,city2 varchar(30)
,State2 Varchar(30)
,zip2 varchar(5)
,age int
,CustomGroup1 varchar(30)
,CustomGroup2 Varchar(30)
,CustomGroup3 varchar(30)
)
My first attempt at this was running a stored procedure that would add a record for each customer each day to this table with their current info. When I did that I added a col for date and inserted the current date. The problem with this is the table grows in size crazy fast and we are concerned with disk space.
Is there a way to keep a record this detailed without taking up so much disk space.
This is on SQL Server 2012.
You should use slowe changing dimension. You can normalize your table to spare disk space, but you coudl hit a problem with performance.