+2  A: 

I would strongly urge you to copy the price from the schedule directly to Purchase as soon as you're inserting that. That way, you have solved your problem, and at the same time, prevented from a different price being charged to the customer later on if you accidentally (or intentionally) change the schedule.

As for relating to the primary key of schedule, and this not being traceable from a transation: that is a sign of bad design. I mean, think about it - you have a timestamp in transaction, a schedule by definition is placed in time using a from and to timestamp - why can't you relate them? AFAICS, primary key of schedule should be item_id, from_timestamp, to_timestamp

Assuming your schedule table has a from and to timestamp My query would be

SELECT     ..your columns..
FROM       Transaction t
INNER JOIN Purchase    p
ON         t.id        = p.transaction_id
INNER JOIN Item        i
ON         p.id        = i.purchase_id
INNER JOIN Schedule    s
ON         i.id        = s.item_id
AND        t.timestamp BETWEEN s.from_timestamp
                           AND s.to_timestamp

As for, should you use a view or not - really, it's up to you. A view does not work better or worse than a query, the only difference is that the definition is stored in the database. The main advantages of that are

  • people can reuse the definition without copying the query (and messing it up), the
  • you can change the schema to some extent and hide that from the application provided you update the view accordingly (this latter advantage is often overestimated)
Roland Bouman
"that" as in the purchase, or the schedule info? It solves the problem, but the scheduling system works as expected when handling those events - this query is ONLY for retrieving data that has already been entered.
Derek Adair
that as in the purchase.
Roland Bouman
and oddly enough, the system already does this (add's the price to purchase) - me and my colleague, who didn't build this DB, agreed that the price should probably be omitted from purchase
Derek Adair
+1: Capture the price in the `PURCHASES` table at time of purchase. If the price value changes in the `SCHEDULES` table afterwards, tabulation of the bill will be incorrect on subsequent invoice reprints/etc.
OMG Ponies
The system probably does this because it was built by someone with experience building retail systems :). Look, you can organize it and only store price in the schedule table, but you're going to have to prevent anyone from updating the price in any current timeslots, or you'll be in a world of pain. Correcting a price can only be done by creating a new schedule.
Roland Bouman
Once a price value is determined for a given item - it cannot change. I misunderstood what you were saying.
Derek Adair
not even a typo? :)
Roland Bouman
yes, I completely agree - it's a bit absurd. Now to articulate so my stubborn and anal boss will agree this whole DB needs a facelift ;P
Derek Adair
Ok. Well I don't want to question the design further. Thanks for accepting the answer - good luck with the redesign, and I hope your boss isn't being to pointy haired :)
Roland Bouman
A: 

Can't the transaction record have a foreign key linked with the schedule primary key? or is it a many-to-many relationship. Either way I do not see view as appropriate here.

Samuel
Why is view not appropriate?
Derek Adair