How to redesign an existing MS Access table in SQL server

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. Wh开发者_开发技巧ere 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:

• 8/1/11 account 1234567890 gets added with a debit of -$10, a rep assigns status = reviewed, reason = check issue
• 8/2/11 account 1234567890 gets added with a new debit of -$50, the start status = reviewed, reason = check
• 8/2/11 - user works the data on account 1234567890 for debit $-50 and changes the status = resolved, reason = none
• 8/3/11 - user performs a search for business date 8/1/11 for account 1234567890 they need to see the data with status = reviewed, reason = check issue

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!

It appears that you have a mixture of a data storage requirement and a workflow requirement. SQL Server is great for storing data, but you have several options available to manage the workflow element. You can either use a stored procedure to manage your workflow or SQL Server Integration Services (SSIS).

The problem outlined appears to only need the latest daily snapshot (one for each day the data changed), so a daily view of the latest snapshot would suffice. Alternatively, if you actually need to record and be able to trace all intraday transactions, then you may want to split the table into two (a transaction and daily snapshot), but I feel this is probably not necessary.

Stored Procedure Solution

To keep everything within SQL Server, you would use a procedure that creates, inserts and copies your data accordingly, depending on what already exists,

The following script sets up a table called AccountDetails with a single sample data account;

    CREATE TABLE [dbo].AccountDetails(
    [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]

INSERT INTO AccountDetails
VALUES ('2011-01-01', 123, NEWID(), 20, '2011-01-01', 'initial', 'reviewed', 'check issue', '2011-01-02', 'dwb',GETDATE())

The stored procedure AccountDetailsController enables you to either

• Update an existing account that has an entry for today. Fields passed in as null will assume the previous records value.
• Insert a new record based on a previously entered account details. This caters for the scenario of having an account on already setup, but wanting to update certain details. Fields passed in as null will assume the previous records value
• Insert a new record, no previously entered account details. New account, all fields are inserted for the first time

.

CREATE PROCEDURE AccountDetailsController
    @BusinessDate smalldatetime ,
    @Account [int] ,
    @Guid [uniqueidentifier] ,
    @InitialAmount [money] ,
    @LetterDate smalldatetime,
    @LetterType varchar(50) ,
    @Status varchar(50) ,
    @Reason varchar(50) ,
    @FollowUpDate smalldatetime,
    @LastModifiedBy varchar(50)
 AS
 BEGIN

/*
 -- test bed
 -- remove when finished testing
    DECLARE 
    @BusinessDate smalldatetime = '2011-01-01',
    @Account [int] =123,
    @Guid [uniqueidentifier] =null ,
    @InitialAmount [money] =30 ,
    @LetterDate smalldatetime = '2011-01-01',
    @LetterType varchar(50) ='initial',
    @Status varchar(50) =  'reviewed',
    @Reason varchar(50) = 'check issue',
    @FollowUpDate smalldatetime = null,
    @LastModifiedBy varchar(50) = 'dwb'
*/

    IF EXISTS (SELECT * FROM AccountDetails WHERE DateDiff(DAY,BusinessDate, @BusinessDate) = 0 AND @Account = [account])
    BEGIN
        --RECORD ALREADY EXISTS FOR TODAY, UPDATE
        UPDATE  AccountDetails
        SET     BusinessDate = ISNULL(@BusinessDate, BusinessDate),
                [guid] = ISNULL(@Guid, [guid]),
                InitialAmount = ISNULL(@InitialAmount, InitialAmount),
                LetterDate = ISNULL(@LetterDate, LetterDate),
                LetterType = ISNULL(@LetterType, LetterType)  ,
                [Status] = ISNULL(@Status ,[Status] ) ,
                Reason = ISNULL(@Reason, reason) ,
                FollowUpDate = ISNULL(@FollowUpDate, FollowUpDate),
                LastModifiedBy = ISNULL(@LastModifiedBy, LastModifiedBy) ,
                LastModifiedDate = GETDATE()
        FROM    AccountDetails ad 
        WHERE   DateDiff(DAY,BusinessDate, @BusinessDate) = 0 
        AND     @Account = [account]
    END 
    ELSE IF EXISTS (SELECT * FROM AccountDetails WHERE @Account = [account])
    BEGIN
        --RECORD ALREADY EXISTS, BUT FOR ANOTHER DAY.  USE THIS AS THE BASIS OF TODAYS RECORD
        INSERT INTO AccountDetails
        SELECT @BusinessDate,
                @Account,
                ISNULL (@Guid, ad.guid),
                ISNULL(@InitialAmount, InitialAmount),
                ISNULL(@LetterDate, LetterDate),
                ISNULL(@LetterType, LetterType)  ,
                ISNULL(@Status ,[Status] ) ,
                ISNULL(@Reason, reason) ,
                ISNULL(@FollowUpDate, FollowUpDate),
                ISNULL(@LastModifiedBy, LastModifiedBy) ,
                GETDATE()
        FROM    AccountDetails ad
        WHERE   Account = @Account
        AND     BusinessDate = 
        (   
            SELECT  MAX(BusinessDate)
            FROM    AccountDetails 
            WHERE   @Account = [account]
        )
    END 
    ELSE 
    BEGIN
        --NO PREVIOUS RECORD EXISTS
        INSERT INTO AccountDetails
        VALUES (@BusinessDate,
                @Account,
                @Guid,
                @InitialAmount,
                @LetterDate,
                @LetterType,
                @Status,
                @reason ,
                @FollowUpDate,
                @LastModifiedBy ,
                GETDATE())

    END

END

The following should cater for all scenarios;

--update a record that exists for today
exec AccountDetailsController '2011-01-01', 123, null, 30, '2011-01-01', 'initial', 'reviewed', 'check issue', null, 'dwb'

--create a new record based on the last date entered for account
exec AccountDetailsController '2011-01-02', 123, null, 50, null, 'considered', null, null, null, 'dwb'

--create an entirely new record, no previous account exists
exec AccountDetailsController '2011-01-02', 456, 'C9CA5430-6D0E-47A3-BC89-D95C53B888E4' , 10, '2011-01-03', 'opened', 'new account', 'unknown','2011-01-03','dwb'

This will result in the following;

SSIS Solution

If your business logic and workflow is more complicated than the example you provide, you may want to consider SSIS to manage and control the data. This would take the solution out of SQL and would initially give you a setup overhead, but would provide a clearer view of what is happening to the data and may be more manageable over time - especially with the imported files.