Select most recent dependant record

Select most recent dependant record

I want to implement pricing for a product as follows:
The Product object holds a single Pricing object which describes its
current price.
The Product table is linked to a Pricing table in a one-to-many relationship
+------------+ +---------------------+
| Product | | Price |
+------------+ 1-n +---------------------+
| product_id | ----------- | product_id |
| ... | | price_id |
+------------+ | price_value |
| price_creation_time |
| ... |
+---------------------+
Sounds simple enough so far, but the part I'm having trouble with is
The Product has only one active Price, namely the most recent one.
Any pricing records prior to the most recent one shouldn't be fetched.
When the user edits a product's pricing, the current record should not be
updated, a new one with a current timestamp should be inserted instead.
The previous pricing records are kept for historical reasons (such as
seeing what a customer wanting a refund paid for the product at the time
of purchase).
Essentially, from within the Product object, the relationship should
appear as if it was a one-to-one relationship. However, I don't know how
to implement such a thing using Hibernate
Last Record of one to many relation Hibernate Criteria suggests something
called Criteria may be of help, but I have very little Hibernate
experience and am still getting to grips with the class annotation.
Is it possible to implement a one-to-many relationship such that is
appears to be a one-to-one relationship like this with Hibernate? How
would I go about doing it?
UPDATE: A bit more info on the idea is as follows:
A receipt will consist of a collection of records for each item bought.
+--------------+ +------------------+
| Receipt | | ReceiptLine | +---------+
+--------------+ +------------------+ +----- | Product |
| receipt_id | ----------- | receipt_id | | +---------+
| receipt_time | | product_id | -----+ +-------+
| ... | | price_id | ----------- | Price |
+--------------+ | product_quantity | +-------+
| ... |
+------------------+
Products have a current price (and obviously only one current price) which
is determined by the price record in the price table that has the most
recent timestampe.
SELECT *
FROM prices
WHERE product_id = (Product ID goes here)
ORDER BY price_creation_time;
LIMIT 1;
When you update the pricing, instead of updating the pricing record
currently linked to the product, I want to insert a new one.
INSERT INTO price (
product_id,
price_value,
price_creation_time
) VALUES (
(Product ID goes here),
(New price goes here),
NOW()
);
When a customer buys something, a line is added to the receipt record for
each item bought. Each line consists of the ID of the product, cut also
includes the ID of the pricing record that's applicable at the time the
receipt was generated. That way I'll know what a customer paid for a
product at the time of purchase.
This data could also be used in accounting and other data mining
activities that haven't been defined yet.