CODEWITHSUNDEEP
sqlsql-server
Joshit
Joshit

Reputation: 1325

SQL provide data with predefined rules

I am looking for an elegant way, to provide data, with rules regard to contents.

Let´s assume the following: I want to calculate the discount for customers. I have Customer-Categories, "HappyHours" and the discount.

I have 9 different categories, and for each category there are 3 different Happy-Hour-Times. The discount depends on a combination of category and time. The values won´t change frequently, so I am looking for a "constant" way to solve this issue.

Example:

  1. categoryPremium && timeOne equals 20% discount
  2. categoryPremium && timeTwo equals 22% discount
  3. categoryPremium && timeThree equals 30% discount
  4. categoryRegular && timeOne equals 10% discount
  5. categoryRegular && timeTwo equals 15% discount

and so on...

This is for an application, and the discount is required at each execution. So it would be really nice to find a way, which is clean and fast.

I thought of two possibilities:

  1. A table, with composite primary key (category and happyHour) filled once manually
  2. A hard coded assignment-function (similar to the number of matches between two rows mysql)

What do you think? Is there a way which is more dynamic?

Upvotes: 0

Views: 99

Answers (2)

TJB
TJB

Reputation: 877

From a SQL server perspective, the neatest thing to do is create a table with three columns as per your 1st suggestion (Category, Time, Discount). You then have two options for your application. If it were a .net app you could perform a simple select using your category and time values as variables passed through to return the result set.

Or, if you want less SQL code in your app, you could create the table as above and select from a scalar-valued function created as such:

CREATE FUNCTION [dbo].[fn_Discount]  (@Category Varchar(50), @HappyHour TIME)
RETURNS  DECIMAL(8,2)
AS
BEGIN

 DECLARE @Discount DECIMAL(8,2) = (
    SELECT 
    Discount 
    FROM dbo.HappyHourDiscount
    WHERE Category <= @Category
        AND HappyHour = @HappyHour
    )

    RETURN(@Discount)
END

Passing the parameters for the function via your app.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269803

You should probably have a reference table, with columns such as:

  • Category
  • HappyHourTime
  • Premium

Then you can look up the values using the reference table. Something like:

select t.*, r.premium
from t join
     reference r
     on t.category = r.category and t.happyhourtime = r.happyhourtime;

This is similar to your first choice. However, I would have a synthetic identity primary key and have the other two column have a unique constraint. Also, you might consider turning this into a slowly changing dimension (with an effective and end date), so you can keep track of changes over time.

Upvotes: 1

Related Questions