From: Robert Schaaf <ruby-bucket@...>
Date: 2009-06-08T22:22:44+09:00
Subject: Re: sqlite3 & joins

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/.
>>
>
>