database structure with multiple bank statements

Question

Okay, so I am designing a database to hold transactions and their types, receipts received and then bank account statements.

First step is that receipts are input and a transaction type is created (cash/food/clothes/ect). Sometimes a receipt has multiple trans type records. Second step it to match the transactions in the bank statements (unless cash) to receipts (if any) and to an existing transaction type or create a new one.

I am going to upload all statements from multiple accounts in multiple banks into CSV's and then import them into the DB.

My problem is that I can't get a db structure that feels right. I could:

• Have a separate table for each acct/bank but that feels wrong.
• Combine all CSVs into one with a acct/bank unique ID.
• Combine all CSVs into a single.

This should be a simple design problem but I can't seem to work it out.

I'm also using OpenOffice Base + other Apache apps.

Any help would be appreciated.

Answer 1

Perhaps something like the below TABLE NAMES in all caps with Column Names below each table (top column name for each table is the primary key).

STATEMENTS

• StatementID
• Bank
• StatementDate

STATEMENT_DETAILS

• SDetailID
• StatementID
• DetailDate
• Store
• Amount

TTYPES

• TypeID
• TransactionType

RECEIPTS

• ReceiptID
• Store
• ReceiptDate

TRANSACTIONS

• TransactionID
• ReceiptID
• TypeID
• Amount

So in this scheme, you would complete all the listed fields normally: all primary keys would autoincrement, form/subform linking would be utilized to fill in foreign keys where possible, and the remaining fields would be input by the user.

At that point you would run some sort of query or script to match store name, receipt date, and amount from the RECEIPTS and TRANSACTIONS tables to their counterparts in the STATEMENT_DETAILS table. You could just view the results from the query, or save the SDetailID in the TRANSACTIONS table, or save the TransactionID in the STATEMENT_DETAILS table.

You would have to work out how to get the data from your various CSV files into the STATEMENTS and STATEMENT_DETAILS tables.