Re: Re: Fwd: Silly question about numbering of rows?

On Tue, 13 Mar 2001 09:55:54 Ken Corey wrote:
>> > id | make | model | year | value
>> > ---------+-----------+--------+-------+-------
>> > 57 | 2 | 0 | 4 | 4750
>> > 57 | 2 | 3 | 4 | 4750
>> > 57 | 2 | 0 | 0 | 4750
>> > 57 | 2 | 0 | 3 | 4750
>> > 57 | 2 | 3 | 0 | 4750
>> > 57 | 2 | 3 | 3 | 4750
>> > 2 | 2 | 0 | 3 | 4750
>> > 2 | 2 | 3 | 3 | 4750
>> > 2 | 2 | 0 | 4 | 4350
>> > 2 | 2 | 3 | 4 | 4350
>> > 2 | 2 | 0 | 0 | 4750
>> > 2 | 2 | 0 | 5 | 4750
>> > 2 | 2 | 3 | 0 | 4750
>> > 2 | 2 | 3 | 5 | 4750
>>
>> The potential for duplicate records is rampant here. What is the primary
>> key? Do these values use foreign keys?
>
>The primary key is id/make/model/year, sorted by sum(value).
>
>Doh! I think I forgot to mention a critical part: selects on the table vary
>
>model and year to be either '0' (which represents 'all') or the specific
>number concerned.
>
>So, there are 4 ways to slice this data:
> a particular make, all models, all years
> a particular make, particular model, all years
> a particular make, all model, particular years
> a particular make, particular model, particular years

It seems to me that you are misusing the WHERE clause in your queries. Why
not just select like this:

SELECT *
FROM tblname
WHERE make='$make';

SELECT *
FROM tblname
WHERE make='$make' AND model='$model';

SELECT *
FROM tblname
WHERE make='$make'
AND YEAR >= '$lowyear' AND YEAR <= '$highyear';

SELECT *
FROM tblname
WHERE make='$make'
AND model='$model'
AND YEAR >= '$lowyear' AND YEAR <= '$highyear';

That way you don't need a zero in your column values to simulate 'all'. This
will ensure each row actually represents an automobile and not some
artificial entry created for use in queries.

Your application logic will only be a little more complicated, and your
queries will be a lot faster, especially as your table size grows. In Perl,
I'd do something like this:

$sql = 'SELECT * FROM tblname ';
if ($make > 0 && $model == 0 && $year == 0) {
$sql .= "WHERE make='$make'";
} elsif ($make > 0 && $model > 0 && $year == 0) {
$sql .= "WHERE make='$make' AND model='$model'";
} elsif ($make > 0 && $model == 0 && $year > 0) {
$sql .= "WHERE make='$make' AND year='$year'";
} elsif ($make > 0 && $model > 0 && $year > 0) {
$sql .= "WHERE make='$make' AND model='$model' AND year='$year'";
} else {
# Some error-handling code here
}

Tony
--
Anthony E. Greene <agreene(at)pobox(dot)com> <http://www.pobox.com/~agreene/>
PGP Key: 0x6C94239D/7B3D BD7D 7D91 1B44 BA26 C484 A42A 60DD 6C94 239D
Chat: AOL/Yahoo: TonyG05 ICQ: 91183266
Linux. The choice of a GNU Generation. <http://www.linux.org/>