Table setup decision

I'm setting up a checking/saving/budget database for myself to replace one I set up for myself some years ago.

I've run into a dilemma while setting up the tables.

Transactions (tblTransaction) have attributes like date, payee (queried from tblPayee), amount, and type (either debit or deposit). Of course, debit-type transactions have one payee and one amount. But for deposit-type transactions, I'd like to be able to record where the money came from when the money comes from multiple sources. For instance, say I go to the bank to make a $100 deposit. Maybe $50 of the $100 was cash from returning something to a store, $25 was a check from my employer reimbursing me for something, and the last $25 was misc cash from a drawer in my house.

So that's a $100 transaction that has 2 sources (aka payees, since I want to query them from tblPayee, they're really the same thing) with 2 amounts I want to record, and the remaining $25 that has no specified payee/source.

What's the best way to set these tables up?

Thanks.

Part 2: What is the term for what I'm asking about? Is it "db schema"? just "db design"? "db structure"? none of these? all of these?

Hi CDF,

You can go about solving the problem in two ways.

1. Data Level - You can keep the existing database layout of two tables (transactions, payee) and split the bank deposit into different rows accordingly when the data is inserted into the table.

Transaction table
ID | Date | Payee | Amount | Type
1 | 01/01/10 | 1 | 50 | Debit
2 | 02/01/10 | 2 | 50 | Deposit
3 | 02/01/10 | 3 | 25 | Deposit
4 | 02/01/10 | 4 | 25 | Deposit
----
Payees
1 – ATM
2 – Store
3 – Employer
4 - Misc

This way you would be keeping the one to one relationship between the two tables and no data is lost. This is the easy way.

2. Design Level - If you want to have just one record in the transaction table for the bank deposit then you have to add a third table (ex transactionSource) which acts as an index table. The payee field in the transaction table can be deleted.

This table would handle the Many (payee) to One (transaction) issue you are encountering.

Using the above data, the tables would look something like;

Transaction table

ID | Date | Payee | Amount | Type
1 | 01/01/10 | 1 | 50 | Debit
2 | 02/01/10 | 2 | 100 | Deposit

Transaction Source

SrcTransactionID | SrcPayeeID | Amount
1 | 1 | 50
2 | 2 | 50
2 | 3 | 25
2 | 4 | 25


One slight problem with the schema is that the amounts are replicated in two tables. A further enhancement would be to delete the amount field from the transaction table and SUM up the amounts from the TransactionSource table when the total for the transaction is require.

To answer your second part, you are referring to the database schema.

Kurt