I am in the middle of rewriting an MS Access database that currently is not normalized and is very poorly designed. My issue in this redesign is surrounding the way they move data from day to day between the tables.
The current set up is similar to this. I have a text file that gets loaded in a table. The records that I need get added to Table1, the next day I some of the data from Table1 is loaded into Table2. Table2 is then used to update Table1 after the import for the day.
6/1/11
File > Table1
field1
field2
On 6/1/11 the file will update Table1 and populate the fields
6/2/11
Step 1 Step 2 Step 3
Table1 > Table2 File > Table1 Table2 > Table1
field1 field1 newdata1 field1 newdata1
field2 field2 newdata2 field2 newdata2
On 6/2/11, the first step is that field1/field2 get moved to table2 (a temp table). We then delete the data from table1 and then import the file from that day in Step 2. In Step 3, we perform an update on table1 using the data from table2 if the account is present. Basically, we are bridging yesterday's data forward for today if the account exists.
In my new design I have a table similar to this. Where the primary key in the table would be the BusinessDate and the Account because those are distinct from day to day.
CREATE TABLE [dbo].[Table](
[BusinessDate] [smalldatetime] NOT NULL,
[Account] [int] NOT NULL,
[Guid] [uniqueidentifier] NOT NULL,
[InitialAmount] [money] NULL,
[LetterDate] [smalldatetime] NULL,
[LetterType] [varchar](50) NULL,
[Status] [varchar](50) NULL,
[Reason] [varchar](50) NULL,
[FollowUpDate] [smalldatetime] NULL,
[LastModifiedBy] [varchar](50) NULL,
[LastModifiedDate] [datetime] NULL
) ON [PRIMARY]
If an account was added 6/1 and then was imported on 6/2 I would perform an update using the data from 6/1 on the fields above. I have been advised that continuing to do this same kind of copy data forward from day to day is a bad design. But I don't know how to implement this because the records from day to day are considered new but the some of the original data needs remain attached to all future items.
This type of set-up is used all over the current set-up and I am stumped on how to design it. Can anyone offer any suggestions on how to proceed with this design? This is difficult to explain so if it isn't clear please ask.
EDIT:
Part of my problem is that I need to be able to access the details that get moved forward for any day. If I have an account that comes in 8/1/11 with an initial amount on the account, the users assign a status, letter type. And then that same account comes in 8/2/11, the starting point for that for 8/2 is the end of day values from 8/1. They would then process the account for that day. On 8/2 the account will start over but instead of from nothing, it starts with yesterday's values.
The users, still need to have the ability to access the data from 8/1 and see the end of day values.
Example:
Hopefully the extra details will help. Sorry for the very long explanation.
Ok, so having read the edits to your question, here's my next try!
The idea behind this is that we're using one table to store everything, and this table is effectively a changelog for everything that happens. Then, instead of working directly on the table, we use a view to give us the latest information for each account. I've also included a view that gives you the "closing" state for each account by day.
You can easily modify this design to have only one row per day, instead of it being a full changelog. (In that case, you don't need the second view, as it's effectively the same as the table.)
CREATE TABLE Accounts (
ID int IDENTITY(1,1),
BusinessDate smalldatetime NOT NULL,
Account int NOT NULL,
InitialAmount money NULL,
LetterDate smalldatetime NULL,
LetterType varchar(50) NULL,
Status varchar(50) NULL,
Reason varchar(50) NULL,
FollowUpDate smalldatetime NULL,
LastModifiedBy varchar(50) NULL,
LastModifiedDate datetime NULL
)
insert into Accounts
values ({d '2011-08-01'}, 1234567890, -10, NULL, NULL, 'Reviewed', 'Check issue', NULL, 'User', NULL),
({d '2011-08-02'}, 1234567890, -50, NULL, NULL, 'Reviewed', 'Check issue', NULL, 'DataLoad', NULL),
({d '2011-08-02'}, 1234567890, -50, NULL, NULL, 'Resolved', 'None', NULL, 'User', NULL),
({d '2011-08-02'}, 1234567891, -20, NULL, NULL, 'Reviewed', 'Check issue', NULL, 'DataLoad', NULL)
GO
create view AccountsLatest
as
select a.*
from Accounts a inner join
(
select a.Account, MAX(a.ID) as ID
from Accounts a
group by a.Account
) mx on a.ID = mx.ID
GO
create view AccountLatestByDate
as
select a.*
from Accounts a inner join
(
select a.Account, a.BusinessDate, MAX(a.ID) as ID
from Accounts a
group by a.Account, a.BusinessDate
) mx on a.ID = mx.ID
GO
As for your file that's loaded in, and needing to "copy forward" some data, well that can be handled something like so:
--For simplicity's sake, I'm creating a load table that you simply clear and append your file contents to every night
create table MyFile (
BusinessDate smalldatetime NOT NULL,
Account int NOT NULL,
InitialAmount money NULL,
)
insert into Accounts
select f.BusinessDate, f.Account, f.InitialAmount, a.LetterDate, a.LetterType, a.Status, a.Reason, a.FollowUpDate, a.LastModifiedBy, a.LastModifiedDate
from MyFile f inner join
AccountsLatest a on f.Account = a.Account
I hope this works as a somewhat ok starting point for you - it obviously needs refining to fit your exact needs.
I think you're right to question the process behind this, as I think the solution that's currently in place is a little bit squidgy. It may work, but it sounds far from ideal. I don't know anything about your business, but the business process behind it may be a little squidgy too - it may be worth taking the opportunity of this platform migration to at least explore the idea of cleaning that side up a little bit too.
ORIGINAL ANSWER (for posterity's sake)
If you've got data about the accounts that remains static from day to day, and other data that is date-specific, then I would look to separate those sets of data into their own tables. This should eliminate the need to copy data from one day to the next. So your tables might look something like this (I'm guessing as to which columns remain static):
CREATE TABLE dbo.Accounts (
Account int NOT NULL PRIMARY KEY,
InitialAmount money NULL,
Status varchar(50) NULL,
Reason varchar(50) NULL,
LastModifiedBy varchar(50) NULL,
LastModifiedDate datetime NULL
)
CREATE TABLE dbo.AccountDetails (
BusinessDate smalldatetime NOT NULL,
Account int NOT NULL,
Guid uniqueidentifier NOT NULL,
LetterDate smalldatetime NULL,
LetterType varchar(50) NULL,
FollowUpDate smalldatetime NULL,
LastModifiedBy varchar(50) NULL,
LastModifiedDate datetime NULL
)
--This table will contain all the historic data that used to be in AccountDetails
CREATE TABLE dbo.AccountDetailsHistory (
BusinessDate smalldatetime NOT NULL,
Account int NOT NULL,
Guid uniqueidentifier NOT NULL,
LetterDate smalldatetime NULL,
LetterType varchar(50) NULL,
FollowUpDate smalldatetime NULL,
LastModifiedBy varchar(50) NULL,
LastModifiedDate datetime NULL
)
Now, every night the contents of AccountDetails is moved to AccountDetailsHistory, and the contents of the file loaded into AccountDetails. The data that you would have had to "copy forward" is stored in Accounts, and so hasn't changed.
You could also simply have one AccountDetails table, and partition it, if you needed to. Here's a decent article on how partitioning works and whether you should use it: http://msdn.microsoft.com/en-us/library/ms345146%28v=sql.90%29.aspx, and this one shows you how simple it is to set up: http://blogs.msdn.com/b/manisblog/archive/2009/01/18/easy-table-partitions-with-sql-server-2008.aspx.
Hope this helps!