Great and thoughtful question! This is a common scenario in applications tracking changing state over time, and you’ve already outlined the main trade-offs between normalization (single source of truth) and denormalization (performance).
Here’s how I would approach it:
1. Denormalize Current Prices on the Products Table
Yes, keep the price_purchase, price_sell, and price_advice columns on your products table.
This denormalized approach has these advantages:
- Fast Reads: Most of your application probably needs the current price, not the entire history. Accessing these fields directly is extremely fast—no additional queries, joins, or subqueries.
- Simplicity: Your UI code and logic for index/listings remain simple and high-performing.
2. Maintain Historical Records for Auditing/Analytics
Your product_prices table continues to store the full price history. This is the right place for analytics, audit logs, price trends, reporting, etc.
3. Keep Them in Sync with Database Transactions or Events
Whenever you change a price, do these two things:
- Write a new row in
product_prices - Update the product’s "current price" column
If you’re using Eloquent, you could do this in your service/model logic or using model events:
public function updateProductPrice(Product $product, $type, $value, $publisher)
{
// 1. Log price change
ProductPrice::create([
'product_id' => $product->id,
'price_type' => $type,
'price_value' => $value,
'publisher_id' => $publisher->id,
'publisher_name' => $publisher->name,
]);
// 2. Update denormalized column on products
$column = "price_{$type}";
$product->$column = $value;
$product->save();
}
And to ensure atomicity, wrap both in a transaction:
DB::transaction(function() use ($product, $type, $value, $publisher) {
// ...both steps as above...
});
4. Document Your Design Decision
It’s perfectly acceptable to denormalize in this scenario—many successful applications make this choice (even at massive scale, e.g. Shopify, Amazon, etc.). Just be clear in documentation and code comments so future maintainers understand the synchronization constraints.
5. Alternatives Evaluated
- Subqueries/Views: Can be elegant, but for large data sets they become a bottleneck and complicate Eloquent relationships and eager loading.
- Database Triggers: Possible, but can be harder to maintain/debug and are less portable.
- Caching: Could offload some reads, but you’d still need to update cache upon each write, and full denormalization is simpler for this case.
Conclusion
Denormalize. Your products table fields are the source of truth for “current” business logic, while product_prices provides full history. The key is careful synchronization, but Laravel (and good database practices) make this straightforward. This approach maximizes performance and clarity for the needs you’ve described.
Summary:
- Keep current prices denormalized on
products - Record price changes in
product_prices - Synchronize both in a transaction
- Reads are fast, history is preserved, models stay simple
Let me know if you want specific Eloquent/scoped query examples for fetching price history!
