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

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

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

Buy PeopleSoft.