Best approach for displaying a price from a product according the

[Jose_49]

I'm basically building a reseller website, in which people can buy (users) from a predefined product list and sell them at a higher price (clients).

I will show you some images to explain better myself.
The following image represents a table with several fields, in which I have included only 2 columns.

The point of this table is to allow the user to fully customize the price displayed for his clients.

Let me explain: I want to allow the user to override the default price according to the setting combined.


For example: If a user has the "Global" in scope and "Percentage" in mode. This means that EVERY product will appear with a percentage price increase.

If a user has the "Client" in scope and "Percentage" in mode. This means that every product will appear with a percentage price increase ONLY for the client specified.

But, where's the catch? In overriding. Since the same user can have multiple settings, many of them will match criteria in-between.  

See the following picture:

The number 4 is the most specific, hence the one who weights the more and will override the other 3 (if criteria matched).

Example:
Imagine I have the following scenario:
Imagine there are fertilizers as products. A user wants to sell to "Axel", the client.

For all fertilizers sold, the user will bill 15% of the original price. But, the client "Axel", instead of being billed 15% of the original price, he will be billed 10%.


When I look into the database table, I see that the user has set "Global" as its scope and "Percentage" as mode. Nonetheless, this same criteria is met because the user has also set "Client" as its scope and "Percentage" as mode in another row.

I'd like to "override" that global setting for "Axel" and display the billed 10%

This is the approach I have thought for now:


 
So this is what I'll be doing.

When I receive a request to add a product to the client I would be doing the following:
 

    SELECT * FROM `table` WHERE scope = 4 && Product_ID = 12 && CLient_ID = 1
    
    If match is not found or value has not been returned, then find this
    SELECT * FROM `table` WHERE scope = 3 && Product_ID = 12
    
    If match is not found or value has not been returned, then find this:
    SELECT * FROM `table` WHERE scope = 2 &&  && CLient_ID = 1
    
    
    If match is not found or value has not been returned, then find this
    SELECT * FROM `table` WHERE scope = 1

What is the best way to handle this situation? If it's of any interest I'll be using ASP.NET MVC 5 with this. <br/><br/>Thanks a million and sorry for the long question.

 

Also posted in here:

http://programmers.stackexchange.com/questions/258510/best-approach-for-displaying-a-price-from-a-product-according-the-setup-desi

[+theblazingangel MVC]

Understand that although theoretically it is preferable to design an efficient database through following "normalisation", sometimes real world scenarios actually require you to avoid some normalisation in order to achieve optimal efficiency. You should hopefully recognise that your table design is ignoring normalisation, though as I just said, for real world design, this may be okay.

 

Also understand that database queries are a relatively expensive operation. You could perhaps optimise things a little by using a stored procedure, so there's only one query communication between the application to the database rather than up to four. Another option, probably a better one, would be to combine those four queries you've got into one, to grab all four records (or as many as exist for the specified product, client and user combination) in one go, and then loop through them in application code to determine which takes effect.

 

You could also switch to just three modes from four: global; client-specific [product_id = null or specific]; product-specific [client_id = null or specific]. It would be best here to set a unique key on the combination of user_id + client_id + product_id fields to help enforce the rule that only one record can exist for every unique user_id + client_id + product_id combination. Though this would obviously need to accept nulls which may or may not be a problem. The 'scope' field could be eliminated as unnecessary.

SELECT * FROM `table` WHERE user_id = x AND ((client_id IS NULL AND product_id IS NULL) OR (client_id = y AND product_id IS NULL) OR (client_id IS NULL and product_id = z) OR (client_id = y AND product_id = z)

// up to four results returned
// loop through all results in app code to determine which to use

[Jose_49]

No ideas? :p

[Jose_49]

You should hopefully recognise that your table design is ignoring normalisation, though as I just said, for real world design, this may be okay.

 

You could also switch to just three modes from four: global; client-specific [product_id = null or specific]; product-specific [client_id = null or specific]. It would be best here to set a unique key on the combination of user_id + client_id + product_id fields to help enforce the rule that only one record can exist for every unique user_id + client_id + product_id combination. Though this would obviously need to accept nulls which may or may not be a problem. The 'scope' field could be eliminated as unnecessary.

SELECT * FROM `table` WHERE user_id = x AND ((client_id IS NULL AND product_id IS NULL) OR (client_id = y AND product_id IS NULL) OR (client_id IS NULL and product_id = z) OR (client_id = y AND product_id = z)

// up to four results returned
// loop through all results in app code to determine which to use

Thanks a lot!

 

Now that you said it... yeah, it seems that the table was completely de-normalized. I like more your approach because it keep things cleaner. About the null values, I see no problems with that.

 

It will be a little tedious, but I'll just merge the "Scope" and "Mode" column into one.