Handling a column in a database which can change on external conditions

Question

Let's suppose we have a table of products, similar to this one:

ProductId	ProductFriendlyName	ProductPrice
84	Coke can	2

When a specific condition applies (the actual condition isn't really important: it could be "on every thursday", "when it is raining in Paris"), the price can be modified.

ConditionId	AffectedProduct	OverridePrice
43	84	3

I have thought of two solutions:

1. Client side: when the client needs the price of an item an external service is used to fetch the specific price:

int getSpecificPrice(int productId){
var activeCondition = getActiveCondition(productId);
if (activeCondition != null)
{
return activeCondition.OverridePrice;
}
else 
{
var prod = getProdById(productId);
return prod.ProductId;
}
}

2. Database side: a view is used to fetch the data, the price the client gets is already modified to consider any external condition.

Which solution would be cleaner? Is there any other approach to consider?

• Oren A: How do you determine if the condition applies? Is it by server side logic? By something that can be easily retrieved within the DB?
• Acerbic: I would say it is almost certain that the condition can be easily retrieved with a simple join with another table

Answer 1

Assuming checking the condition in a view is not a big deal, and will not make you create some monstrous monolith, I would say a View is definitely the preferred way to go.
Think about it this way: When you'll have a bug and the price will not be displayed correctly, do you want to go find out way by finding the relevant condition in code, and computing it in your head or by debugging, or by looking at the View results and figuring out why they were retrieved?
Often, with Views even system analysts can have a look, unlike code.

Answer 2

Does your database actually have access to the information needed if the condition for the alternative price are true? For example, does your database have knowledge of the weather in Paris?

If your database doesn't have this knowledge, or isn't guaranteed to have this knowledge for all conditions that the business can come up with, then a database view is not really going to help you.

Thus, evaluating this logic can best be done in the server that sits between the user and the actual database itself.