Select most recent dependant record

Question

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.

You could create an updatable view that only shows the latest price and use that from within Hibernate (not sure if that's possible with Hibernate - I don't use it) — , Sep 08 '13 at 08:37
Changing data model to better reflect real relations and use cases is not an option? — Ondrej Bozek, Sep 08 '13 at 09:00
@OndrejBozek: what's wrong with the data model? It's a common pattern to model versioned prices (although I usually use a valid_from/valid_to approach instead of a single timestamp) — , Sep 08 '13 at 09:05
I'm not saying it's wrong. For me it just seems (just from this limited description) that important and generaly accesed price is the last price. So I would probably modeled explicit connection to this last `Price` from `Product`(eg one-to-one). And then connect it somehow with it's history. But as I said I don't know problem domain as well as you. — Ondrej Bozek, Sep 08 '13 at 09:18
What prevents you from limiting access to the historical prices? Wouldn't them just put on backup. — Roman C, Sep 08 '13 at 09:20

score 0 · Answer 1 · answered Sep 08 '13 at 11:37

Practically, the best solution is to have an explicit FK from PRODUCT -> PRODUCT_PRICE.

Preferred:

<one-to-one name='currentPrice' class='ProductPrice' column='FK_CURR_PRICE' 
    fetch='join' />

You can map a inside a to subselect the ID of the most-recent price, but it's not performant or robust concurrency-wise.

Indirect, not preferred:

<one-to-one name='currentPrice' class='ProductPrice'>
    <formula>
        (select MAX(ID) from PRODUCT_PRICE where PRODUCT_PRICE.FK_PRODUCT=this.ID)
    </formula>
</one-to-one>

score 0 · Answer 2 · edited May 23 '17 at 10:25

I think what you should look into Slowly Changing Dimensions:

http://en.wikipedia.org/wiki/Slowly_changing_dimension

You'll might also find this thread interesting, since it's highly correlated to what you're doing and you'll almost certainly end up in situation similar to the one I was in when I asked it myself:

Temporal database design, with a twist (live vs draft rows)

If I were to try to summarize my own findings from back then (I was designing shopping software as well, even though my question introduced it as blog-related):

The best means (imho) to efficiently query current vs historical rows, is to store the latter in a separate audit table (i.e. price vs price_revs). This allows to sidestep gory-looking and inefficient queries, and keep things fast and streamlined in the customer-facing use-case.

The audit table should get updated as appropriate, ideally but not necessarily using triggers. (In case it matters to you, I ultimately opted for a tsrange type instead of two separate start_date/created_at and end_date/deleted_at fields.)
Be wary of overly zealous foreign key constraints, especially if you introduce cascade deletes. When you delete a product, you might still want to keep it and its pricing history around. If so, you need a deleted status or flag or whatever you opt for. In a sense, your main point of reference should be a revision id, rather than a product_id or a price_id.
In the event you ever want to have the live row coexist with the latest non-live row of the same product (e.g. to have a live pricing while the suggested draft pricing gets validated by some random manager), I found that the better solution is to create a separate price (with a draft status) altogether. Think of it as a new "current" draft branch altogether, à la git, which gets merged (i.e. and deleted from the "current" rows when it is) into the live branch when you no longer need it.

score 0 · Accepted Answer · answered Sep 09 '13 at 09:29

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.

You can achieve this by using Hibernate filters. This will allow you to retrieve some elements of a collection without initializing the entire collection. And in this scenario, you want to fetch only one Price element (the recent one) from the price collection present in your Product class.

Add this filter definition to your Product mapping file (first define a filter using <filter-def/> element within a <hibernate-mapping/> element and then attached the filter to the price collection):

 <hibernate-mapping package="x.y.z">
 <class name="Product" table="product_table_name">
 ....
 <set name="priceList" inverse="true" cascade="save-update" lazy="true">
 ...
 <filter name="priceFilter"
            condition="price_id = (select max(p.price_id) from Price p where 
            p.product_id = product_id)" />
 </set>
 </class>
 <filter-def name="priceFilter"/>
 </hibernate-mapping>

This filter will load the price with max(price_id) which is the recently added price for the product. Now you need to enable this filter for a given session, which can be done as follows:

session.enableFilter("priceFilter");

In order to ensure that you are provided with the Product associated with the recent Price, enable the filter on the session prior to retrieving product data:

session.enableFilter("priceFilter");
Criteria criteria = session.createCriteria(Product.class);
criteria.setFetchMode("priceList", FetchMode.JOIN);
criteria.add(Restrictions.eq("id", productId));
Product product = (Product) criteria.uniqueResult();

With this filter, the relationship will appear as if it were a one-to-one relationship. The associated price list will contain only one Price (most recent one). And if you don't enable the filter with session.enableFilter("stateFilter"), Hibernate will load all the associated prices.

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

Assuming that you have a set of prices associated with a particular product, this can be done as below with a convenience updatePrice method in the Product class:

private Set<Price> priceList = new HashSet<Price>();
...
public void updatePrice(Price price) {
price.setId(null);// Hibernate will execute an INSERT statement

price.setProduct(this);
priceList.add(price);
}

The idea is to set the Id property of Price to null before adding the updated price to the priceList of the product.

If you add a new Price to the price list, this new Price will become persistent according to "Persistence by Reachability". All objects reachable from a persistent instance become persistent either when the original instance is made persistent or just before in-memory state is synchronized with the data store.

Select most recent dependant record

3 Answers3