Personal computing discussed

Moderators: SecretSquirrel, just brew it!

 
Crayon Shin Chan
Minister of Gerbil Affairs
Topic Author
Posts: 2299
Joined: Fri Sep 06, 2002 11:14 am
Location: Malaysia
Contact:

Should I use SQL Joins in this situation?

Tue Oct 20, 2015 4:05 am

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.

SELECT * FROM bank JOIN cash ON bank.category=cash.category WHERE bank.category = ?
Mothership: Thuban 1055T@3.7GHz, 12GB DDR3, M5A99X EVO, GTX470+Icy Vision Rev.2@840/3800, Vertex 2E 60GB
Supply ship: Sargas@2.8GHz, 12GB DDR3, M4A88TD-V EVO/USB3
Corsair: Macbook Air Ivy Bridge
 
just brew it!
Gold subscriber
Administrator
Posts: 50294
Joined: Tue Aug 20, 2002 10:51 pm
Location: Somewhere, having a beer

Re: Should I use SQL Joins in this situation?

Tue Oct 20, 2015 5:44 am

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.
Nostalgia isn't what it used to be.
 
dragmor
Grand Gerbil Poohbah
Posts: 3644
Joined: Mon Sep 23, 2002 7:24 pm
Location: Oz

Re: Should I use SQL Joins in this situation?

Tue Oct 20, 2015 7:13 am

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'
SZ87R6/i5 4560 stock/24GB 2333mhz/840 Evo 250GB/Seagate 2TB/ASUS 760GTX/Dell 2711
Rainbows lie in corded knots
While thunder wakes the sleeping crocs.
 
dmjifn
Gerbil First Class
Posts: 103
Joined: Thu May 15, 2003 4:21 pm
Location: Indianapolis

Re: Should I use SQL Joins in this situation?

Tue Oct 20, 2015 7:27 am

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:
tblBank                      tblCash
-----------------------      --------------------------
20151002, "Food",  5.00      20151002, "Clothes", 55.00
20151002, "Car",  27.00      20151004, "Pets",     3.00
20151003, "Cell", 35.00      20151006, "Food",     7.00
20151004, "Pets", 10.00      20151008, "Food",    10.00

The join will return all rows in the first along with all matching rows in the second, combinatorially. So...
select *
from tblBank join tblCash on tblBank.Category = tblCash.Category
where tblBank.Category = "Food"

20151002, "Food",  5.00, 20151006, "Food",     7.00
20151002, "Food",  5.00, 20151008, "Food",    10.00

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.
 
jeffcutsinger
Gerbil In Training
Posts: 6
Joined: Sun Jun 10, 2012 1:48 pm

Re: Should I use SQL Joins in this situation?

Tue Oct 20, 2015 7:46 am

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.
 
derFunkenstein
Gold subscriber
Gerbil God
Posts: 23989
Joined: Fri Feb 21, 2003 9:13 pm
Location: Comin' to you directly from the Mothership

Re: Should I use SQL Joins in this situation?

Tue Oct 20, 2015 8:02 am

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. :)
I do not understand what I do. For what I want to do I do not do, but what I hate I do.
 
the
Gold subscriber
Gerbil Elite
Posts: 876
Joined: Tue Jun 29, 2010 2:26 am

Re: Should I use SQL Joins in this situation?

Tue Oct 20, 2015 8:07 am

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.
Dual Opteron 6376, 96 GB DDR3, Asus KGPE-D16, GTX 970
Mac Pro Dual Xeon E5645, 48 GB DDR3, GTX 770
Core i7 3930K@4.2 Ghz, 32 GB DDR3, GA-X79-UP5-Wifi
Core i7 2600K@4.4 Ghz, 16 GB DDR3, GTX 970, GA-X68XP-UD4
 
Arvald
Silver subscriber
Gerbil Elite
Posts: 704
Joined: Tue Sep 27, 2011 12:14 pm
Location: Gerbil-land, Canada

Re: Should I use SQL Joins in this situation?

Tue Oct 20, 2015 8:50 am

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.
 
dmjifn
Gerbil First Class
Posts: 103
Joined: Thu May 15, 2003 4:21 pm
Location: Indianapolis

Re: Should I use SQL Joins in this situation?

Tue Oct 20, 2015 9:25 am

derFunkenstein wrote:
Do it the right way, not the fast one. :)

If you say so! ;)
 
derFunkenstein
Gold subscriber
Gerbil God
Posts: 23989
Joined: Fri Feb 21, 2003 9:13 pm
Location: Comin' to you directly from the Mothership

Re: Should I use SQL Joins in this situation?

Tue Oct 20, 2015 11:26 am

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. :lol:

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.
I do not understand what I do. For what I want to do I do not do, but what I hate I do.
 
Crayon Shin Chan
Minister of Gerbil Affairs
Topic Author
Posts: 2299
Joined: Fri Sep 06, 2002 11:14 am
Location: Malaysia
Contact:

Re: Should I use SQL Joins in this situation?

Wed Oct 21, 2015 5:55 am

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!
Mothership: Thuban 1055T@3.7GHz, 12GB DDR3, M5A99X EVO, GTX470+Icy Vision Rev.2@840/3800, Vertex 2E 60GB
Supply ship: Sargas@2.8GHz, 12GB DDR3, M4A88TD-V EVO/USB3
Corsair: Macbook Air Ivy Bridge
 
morphine
Gold subscriber
Gerbilus Supremus
Posts: 11419
Joined: Fri Dec 27, 2002 8:51 pm
Location: Portugal (that's next to Spain)

Re: Should I use SQL Joins in this situation?

Wed Oct 21, 2015 9:55 am

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.
There is a fixed amount of intelligence on the planet, and the population keeps growing :(

Who is online

Users browsing this forum: No registered users and 1 guest