TR Forums

I'm writing a finance program in Python that uses SQLite. I've got my bank transactions, cash expenditures and credit card expenditures in separate tables (each with differing numbers of columns). And I'd like to search all of these tables for expenditures of a certain category (say 'food'). Should it look like this? Or is it better to simply select from each table, and display it together in the program? In my experience, the more stuff I do lower down, the easier it is for upper level layers in my program.

This does not seem like a situation where JOIN is appropriate.

The problem is actually with your schema. It seems to me that a more appropriate schema would be a single table with a column that indicates whether the transaction was bank, cash, or credit.

This is not what joins are for. JBI is right you should probably use 1 table with a transaction type field. However if you want to keep your schema, you should create a view using the union statement to combine the tables and the query the view.

create view AllTransactions as
Select 'BANK' Table, Category, Amount from Bank
union
Select 'CASH' Table, Category, Amount from Cash


Then query the view

Select Table, Category, Amount from AllTransactions where Category = 'xyz'

To kind of explain the "this is not what joins are for" comment - that code won't do what you're hoping. Consider this example:

The join will return all rows in the first along with all matching rows in the second, combinatorially. So...

You see that the Food transaction from tblBank is returned twice... this repetition will only grow. Also, "Clothes" doesn't even exist in tblBank right now, so you'd never be able to return it at all. And even then, this probably isn't the "shape" of the result you're looking for. You probably wanted one line per unique transaction.

Union is better for "treating two tables as if they're one", so to speak.

Another possibility would be to design the tables in a parent-child relationship. In this instance you'd create a Transaction table with an ID and the columns that were shared between the tables (say, Amount, which both bank transactions and cash transactions would need). Then you'd create a Bank table & a Cash table whose primary key is an ID from the Transaction table. So each row in the Bank & Cash table would map to exactly one row in the Transaction table, and each row in the Transaction table would correspond to one row in either the Bank or Cash table.

To retrieve the all the data in one go, you'd do a left join per child table.

Or if you are feeling fancy, you could use an ORM like SQLAlchemy which will create a Transaction class with Bank & Cash classes which would inherit from the Transaction class.

Oh and kudos on using parameters; a lot of people build SQL strings and splice the values in which is slower and vulnerable to SQL injection.

I agree with all this. The "right" way to fix it is to use a different pair of tables - one with all of the transactions including an ID that points to the other, which has transaction types.

The "fast" way to fix it is with unions. It's also a pretty bad band-aid. If down the road you need more transaction types, it's way easier to add a record to the transaction types table that future transactions can use than it is to make yet another table for another kind of transaction and then edit all your stored procedures. In fact, that's a huge pain in the rear, so I would stop what you're doing and re-think the database schema. It's easy to insert the records from the tables you have into a third table with all transaction types and the transaction type ID later, once you've done this.

Do it the right way, not the fast one.

Conceptually it sounds like you just want to do the same query across two tables but produce a single result. Conceptually you can try to try using an OR operator to create a single query but I strongly suspect that it'd be easier to simply do the two queries merge them in your program. To help with this, I would suggest not using SELECT * and narrrow the queries to a common subset of columns between the two tables as that'll help with merging on the program side.

DBA and database programmer here.
I second the idea of a union.

dragmor summed it up nicely.

you can use the union in your select or put it into a view to make it look more like a table to your program.

derFunkenstein wrote:

Do it the right way, not the fast one.

If you say so!

dmjifn wrote:

derFunkenstein wrote:

Do it the right way, not the fast one.

If you say so!

I guess that argument is applicable here if this is just for his personal use, and feature creep can be way worse than this problem, but man...I'm just so used to expandable, highly-normalized SQL data that anything else frightens me.

The biggest caution is that if the OP finds himself creating more tables with the same fields again and again, it's probably time to rethink some of the data structure.

Ah don't worry I can actually change the schema since this is just for my personal use. So I think I will go for a single table with a transaction type column... thanks everybody!

A join can be easily used, in the form of "WHERE category IN ('cash', 'credit')". Add index on "category" field and you're set.

In the OP's defense, I've often made some similar structures, simply due to the fact that sooner or later, a customer will ask me to edit the categories, and it's far easier to add/remove rows from a secondary table than to edit database structures (and possibly code) all the time.