Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Location
    New York/New Zealand
    Posts
    8

    matching GL and Bank records (was "Some advice please")

    Am I doing this the best way?

    I have 2 types of records to keep track of, GL and Bank.

    I have thousands of these added to the DB a day.

    The records get matched to eachother in a many to many relationship. GL records can be matched to other bank or GL records and bank records can be matched to other bank or GL records.

    I have the following tables...
    GLMatched
    BankMatched
    Matched
    GLUnmatched
    BankUnmatched

    When I first get a record it will go into the Unmatched table of its type.
    At some point a user or process will find records from both sides and attempt to match them off. When this happens the records are moved to there matched table and a record is added to Matched.

    Users will almost always be working with the unmatched tables but they may need to query the matched tables as well. Speed is important.

    This is going to be built in MS SQL2000 from what I understand.

    Any ideas suggestions or whatever would help.

    Thanks,
    Mike

  2. #2
    Join Date
    Dec 2004
    Posts
    54

    Confused

    Hi ,
    I'm confused as to why you need 5 tables? You described 2 business entities -

    1) A General Ledger Entity
    2) A Bank (transaction) Entity

    Then you said they related in a many to many fashion?

    See attached drawing

    Vmusic
    Attached Thumbnails Attached Thumbnails Acounting101.gif  
    Last edited by Vmusic; 02-02-05 at 19:52. Reason: My image didn't appear

  3. #3
    Join Date
    Oct 2003
    Location
    New York/New Zealand
    Posts
    8
    Hm, don't know how to see attached pic.

    The reason... Here is the history
    This is already an application that is in place. The thing is it is huge and slow and all the users hate me because of it.

    I believe the original goal was to try to speed up the Unmatched queries. Because this is where the bulk of the user queries are it made these tables smaller by moving matched records to seperate tables.

    So there is no relationship between the unmatched tables and any of the others.

    The reason for the Matched table and the BankMatched and GLMatched is for the many to many matches.

    I am by no means a DBA and I didn't build this. We are looking at a total replacement so if this doesn't make sense then please describe what you think should be done.

    Cheers,
    Mike

  4. #4
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710

Posting Permissions

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