Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2008
    Posts
    20

    Application Design Help

    Hello,
    I need to create an application which works with a database, it's a bank account management application. All transactions should be recorded in some temporary storage and a batch program should update the main database according to the temporary records. (The application will be developed in .NET using C#).

    I thought of creating a DataSet (in memory cache storage) of the records, manipulating them and then the batch program would update the main database with the DataSet...

    I would like to hear suggested technique to implement this? some more "correct" way of doing this...

    Thank you

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Create a staging table in the database.
    Create a sproc that cleanses, verifies, and distributes the data in the staging table.
    Advantage: You can set up multiple imports into the same staging table and they can all use the same code logic.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Nov 2008
    Posts
    20
    staging table - it seems that it's exactly what i need
    is this a regular table which manipulated by sql functions ?
    can you direct me please on some guides for creating staging tables, i can't find much information about it, or how to create it?

  4. #4
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    You create a staging table in exactly the same way you create any other table... It's just a table used to hold the import data temporarily.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    My own method is to create staging tables that either model the current physical architecture of the database, or are completely denormalized to match the anticipated data that will be received. Your staging table should consist primarily of varchar columns with no constraints, so that errors rarely occur when loading data into them. Your stored procedure will actually verify these character columns as valid datetime, int, float, or other datatypes, and note any discrepancies.
    I also add auditing columns on each of my staging tables for recording the datasource, the timestamp of when the data was received, and for noting any errors that occurred during processing.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •