Need table configuration advice

I want to set up a database to track parts, what I know about the
parts are:

PartNumber
Length
Material
Description
Revision
Price

The Revision can change, with it the Length and Price, and the Price
can change over time even if the Revision doesn't. On top of that I
want to keep historical data. I was thinking of setting up three
tables:

TABLE1:
PartNumber
Material
Description

TABLE2:
PartNumber
Revision
Length
Date

TABLE3:
PartNumber
Price
Date

Does that seem like the best arrangement? I'm new to SQL not sure just
how to extract the information for a particular date. I could return
any record who's Date is older than a given date, but that could
return multiple revisions of a single part. How then would I select
only the most recent record from before that date? Thanks for any
ideas.

On Thu, 17 Apr 2008 18:00:48 +0200, <mstorkamp@yahoo.com> wrote:

> I want to set up a database to track parts, what I know about the
> parts are:
>
> PartNumber
> Length
> Material
> Description
> Revision
> Price
>
> The Revision can change, with it the Length and Price, and the Price
> can change over time even if the Revision doesn't. On top of that I
> want to keep historical data. I was thinking of setting up three
> tables:
>
> TABLE1:
> PartNumber
> Material
> Description
>
> TABLE2:
> PartNumber
> Revision
> Length
> Date
>
> TABLE3:
> PartNumber
> Price
> Date
>
> Does that seem like the best arrangement?

Seems OK to me, glad you haven't fallen for the trap of adding a Price to
TABLE2. If Length doesn't always change with Revision you could split
TABLE2 in two other tables like TABLE3.

> I'm new to SQL not sure just
> how to extract the information for a particular date. I could return
> any record who's Date is older than a given date, but that could
> return multiple revisions of a single part. How then would I select
> only the most recent record from before that date? Thanks for any
> ideas.

Current:
SELECT t1.PartNumber, t2.Revision, t2.Length, t3.Price
FROM TABLE1 t1
JOIN TABLE2 t2
ON t1.PartNumber = t2.PartNumber
AND t2.Date <= NOW()
LEFT JOIN TABLE2 t2j
ON t1.PartNumber = t2j.PartNumber
AND t2j.Date <= NOW()
AND t2j.Date > t2.Date
JOIN TABLE2 t3
ON t1.PartNumber = t3.PartNumber
AND t3.Date <= NOW()
LEFT JOIN TABLE2 t3j
ON t1.PartNumber = t3j.PartNumber
AND t3j.Date <= NOW()
AND t3j.Date > t3.Date
WHERE t2j.PartNumber IS NULL AND t3j.PartNumber IS NULL


Latest (date possibly in future?):
SELECT t1.PartNumber, t2.Revision, t2.Length, t3.Price
FROM TABLE1 t1
JOIN TABLE2 t2
ON t1.PartNumber = t2.PartNumber
LEFT JOIN TABLE2 t2j
ON t1.PartNumber = t2j.PartNumber
AND t2j.Date > t2.Date
JOIN TABLE2 t3
ON t1.PartNumber = t3.PartNumber
LEFT JOIN TABLE2 t3j
ON t1.PartNumber = t3j.PartNumber
AND t3j.Date > t3.Date
WHERE t2j.PartNumber IS NULL AND t3j.PartNumber IS NULL

Of a particular date:
Just substitute the NOW() calls in the first example with a desired date..

Be sure to set proper indexes to make the fairly quick.
--
Rik Wasmus

On Apr 17, 12:05 pm, "Rik Wasmus" <luiheidsgoe...@hotmail.com> wrote:
> SELECT t1.PartNumber, t2.Revision, t2.Length, t3.Price
> FROM TABLE1 t1
> JOIN TABLE2 t2
> ON t1.PartNumber = t2.PartNumber
> LEFT JOIN TABLE2 t2j
> ON t1.PartNumber = t2j.PartNumber
> AND t2j.Date > t2.Date
> JOIN TABLE2 t3
> ON t1.PartNumber = t3.PartNumber
> LEFT JOIN TABLE2 t3j
> ON t1.PartNumber = t3j.PartNumber
> AND t3j.Date > t3.Date
> WHERE t2j.PartNumber IS NULL AND t3j.PartNumber IS NULL
>
> Be sure to set proper indexes to make the fairly quick.

Thanks. I had to play with that for a while to figure out the how and
why to the way it works. (does anybody use the term 'grok' anymore?)
The next Stupid Newbie question is: What would the proper indexes be?
Just PartNumber or Date as well?

mstorkamp@yahoo.com wrote:
> On Apr 17, 12:05 pm, "Rik Wasmus" <luiheidsgoe...@hotmail.com> wrote:
>> SELECT t1.PartNumber, t2.Revision, t2.Length, t3.Price
>> FROM TABLE1 t1
>> JOIN TABLE2 t2
>> ON t1.PartNumber = t2.PartNumber
>> LEFT JOIN TABLE2 t2j
>> ON t1.PartNumber = t2j.PartNumber
>> AND t2j.Date > t2.Date
>> JOIN TABLE2 t3
>> ON t1.PartNumber = t3.PartNumber
>> LEFT JOIN TABLE2 t3j
>> ON t1.PartNumber = t3j.PartNumber
>> AND t3j.Date > t3.Date
>> WHERE t2j.PartNumber IS NULL AND t3j.PartNumber IS NULL
>>
>> Be sure to set proper indexes to make the fairly quick.
>
> Thanks. I had to play with that for a while to figure out the how and
> why to the way it works. (does anybody use the term 'grok' anymore?)
> The next Stupid Newbie question is: What would the proper indexes be?
> Just PartNumber or Date as well?


Re: indexes - yes - and it could be a compound index (partnumber,Date or
(date, partnumber).

When designing databases you will want to refrain from using reserved
words as table and/or column names. Date is a reserved word in just
about every database engine out there... For every db - just search for
"reserved word <db engine>"