Day of week/Time of day restrictions for a .NET application

Question

Anyone have any ideas on a good way to model the following scenario in my domain/database for a .NET application? Let's say I have certain discounts that can be applied to sales transactions, but the discounts should only be applied on certain days of the week at certain times. So for example, if a sale occurs on Monday between the hours of 9AM and noon, the discount should be applied, but if a sale occurs on Tuesday between those hours, no discount is applied. I would like to be able to handle multiple time periods within the same day as well (ie - Wednesday from 9AM - noon and 6PM - midnight.

Answer 1

I would simply write a "GetDiscount" method that would implement the logic you are describing. You can use the DateTime object to determine current day and time.

From your description it is not clear why such simple solution wont work for you. What exactly are you expecting?

Answer 2

I've done something similar to this.

Your SalePrice table would look like (damn markdown tables):

SalePriceId Guid, PK
ItemId Guid, FK
StartTime Timespan
EndTime Timespan
DaysOfWeek DaysOfWeek (flag enum)
Discount double

(This is from the code view; Timespan is a .NET class that maps in Sql 2008)

DaysOfWeek is a flag enum containing Sun-Sat.

So to find any sales for an item that are in effect on tuesday, you would do something like

select * 
from SalePrice
where ItemId = @itemGuid
and StartTime <= @time 
and EndTime >= @time
and DaysOfWeek & @DOW = @DOW

This is the basic pattern. Don't expect this to copy-paste work (I'm not sure about my bitwise query stuff), but I'm using this pattern successfully to track current prices.

Answer 3

I would just create the following two classes assuming you apply the discount to a comlete order, not single items.

class Order
{
    Guid Id;
    DateTime TimeStamp;
    Discount Discount;
}

class Discount
{
    Guid Id;
    DayOfWeek Day;
    TimeSpan StartTime;
    TimeSpan EndTime;
    Decimal Amount;
}

Then you can use Order.TimeStamp.DayOfWeek and Order.TimeStamp.TimeOfDay and search through a list of discounts until you find a Discount valid for the processed order and store a reference on the Order object. Mapping this to a database should be quite simple - DayofWeek can be stored as an integer, DateTime and TimeSpan are availiable, too.

The tricky part is constraining the one-to-many relationship between Order and Discount. One could try using triggers or check constraints, but I would suggest to enforce this logic only in the application because you don't gain that much by enforcing it at the database level.