Re: sqlite3 & joins
- From: Robert Schaaf <ruby-bucket@xxxxxxxxxxx>
- Date: Mon, 8 Jun 2009 08:22:44 -0500
On second thought, it doesn't look like your schema does what it ought
to.
You'd think that any customer can contract for a certain job which
requires specific parts which can be used in other jobs.
If so, you'd need
1. a customer table with unique records for each customer, identified
by customer_number;
2. a jobs table describing a job, with a unique key for job_number;
3. a parts table describing a part, with a unique key for part_number;
THEN you need the join tables.
4. a contracts table, with a unique key on contract_number, and an
index on customer_number;
5. a bill_of_parts table, indexed uniquely on job_number.
Then the join would be
customers.customer_number = contracts.cusomer_number
contracts.job_number = jobs.job_number
jobs.job_number = bill_of_parts.job_number
bill_of_parts.part_number = parts.part_number
This too is trivial, and should be quick if your RDB isn't a complete
dog. I don't know of SQLite permits multi-column keys and indexes, so
perhaps, "Woof, woof!"
BS
On Jun 8, 2009, at 8:07 AM, Robert Schaaf wrote:
I don't use SQLite, I'm more of a SQl heavy (DB2, Postgresql,
Oracle) type.
What you're asking for is a plain old, b-flat, vanilla join. I don't
know why you'd be doing this in three separate queries.
Without seeing your schema, or knowing exactly which columns you
want to pull, I'll use '*', which cats all columns in all tables. I
assuming you have a part_number in parts (or maybe a part_name.)
Select *
from customer, jobs, parts
where customer.customer_number = jobs.customer_number
and jobs.job_number = parts.job_number
order by customer.customer_number,
jobs.job_number,
parts.part_number
This should be quite efficient if there are indexes on
jobs.customer_number and parts.job_number, otherwise a sequential
scan will be needed for each of the two joins, and that will kill you.
but really I'm more concerned with getting everything playing in tune
and then when I'm more familiar wit SQLite look at optimizations like
using joins.
I suggest you do the hard work up front. It will save you a lot of
bad design decisions down the line.
Good luck,
Bob Schaaf
On Jun 8, 2009, at 7:28 AM, Dave Lilley wrote:
this question goes on from my previous one about my 1st hurdle using
SQLite3.
what sort of performance hit will you get if you have 3 select
statements (1 for each table/DB file)?
tables are customer, Jobs, parts.
customer has a unique field called customer number.
Jobs has a field for the customer number in it and a unique field
called
Job number.
lastly parts has a field called job number.
not sure how many rows there might be for a customer and rows for
parts
at present.
but really I'm more concerned with getting everything playing in tune
and then when I'm more familiar wit SQLite look at optimizations like
using joins.
an yes ultimately i may move off sqlite to another DB but that's
another
step.
Oh also all this is via DBI.
Active record will be another stepping stone (Feel like I've got too
much to cope with for now but perhaps ultimatly go to active record
too).
cheers,
dave.
--
Posted via http://www.ruby-forum.com/.
.
- References:
- sqlite3 & joins
- From: Dave Lilley
- Re: sqlite3 & joins
- From: Robert Schaaf
- sqlite3 & joins
- Prev by Date: Re: Ruby OS
- Next by Date: Printing out Cucumber step definitions
- Previous by thread: Re: sqlite3 & joins
- Next by thread: Printing out Cucumber step definitions
- Index(es):
Relevant Pages
|
