Schema/Code for respecting different schedules

Question

We accept leads from lead providers. We only allow certain providers to post leads to us during certain days/times. Currently, we have 3 schedules, but we might add 1 or n more in the future:

1. Sunday @ 8AM - Friday @ 3PM -- all hours in between.
2. Monday - Friday from 8AM - 5 PM.
3. 24 hours a day, every day.

In addition, we want to be able to one-off certain hours -- so, we would have told some of the lead providers, "I know you normally post 8 AM - 5 PM M-F, but Monday is a holiday, so don't post then."

The problem is that some lead providers are not setup to handle a schedule (or just ignore it if they want to sell us more leads). So, I now have to add this check into our system at the point in which we take in leads, and first check to see if that lead provider (they identify themselves in the XML they send) is allowed to post leads at this particular time.

I honestly don't know where to begin. Has anyone done this before? How did you handle it, and what is good/bad about your method?

Answer 1

So, any lead provider can post leads to your site if the current time of day falls within one of your schedules, as long as that lead provider is allowed to post leads within that schedule? The above is true unless the current time is an exception?

If that's what you're asking, then I'd say that there's several database tables that will need to exist:

1. LeadProvider (Name, unique id, etc)
2. Schedule (Start date, End Date, Start Time, End Time)
3. Link/Bridge table that links the LeadProvider table to the Schedule table (a record in this table with foreign key references to tables 1 and 2 means that a leadprovider is allowed to post leads during this time
4. Exception table - looks like the schedule table, but also has a foreign key reference to the schedule table, and a record in this table means that a leadprovider is not allowed to post leads to your site if the current time falls within this exception time
5. Lead table - keeps track of a lead that a LeadProvider gave - this could also be used to keep track of whether a posted lead fell within an allowed time.

That's something pretty close to how I would build the database side. The code simply needs to support this model (if I'm understanding your question correctly)

Answer 2

There's a few issues you should resolve in order to solve the problem.

A.  Authentication
B.  Authorization

Since you said the client is sending you an XML schema with their information, I hope you have a method of securely authenticating the client in place. If they are already trying to bypass security, it will not be long before they try impersonating other clients to continue bypassing security.

Problem B requires that you verify that the user is authorized to perform the action, or "post a lead." Depending on your system design, this can be as simple as

class User
{
  bool IsAuthorized(DateTime Now) { }
}

check prior to performing the action. You should also define how the time will function. Is it all central standard time, eastern...? Client's should be aware of this restriction and ideally notified via an error message.

Answer 3

I would separate the data used to define the schedule from the generated schedule times - the logic to define a schedule will change a lot, probably, but the logic to tell if a given time falls in a schedule probably won't. However, it's still reasonable to store only the definition of the schedule if you think you can encapsulate the logic to determine if a given time falls in a schedule. If the check is mostly happening outside a DB this might not be too bad, but if you will be writing a lot of SQL that needs to know this, needs to use indexes, etc. then it's harder and you would be more likely to want generated schedule times.

For the generated tables, Tim Claason has a reasonable schema. For the tables that create the schedule, they will pretty much record the options people entered on a data entry screen. These may change - day of week, day of month, skip holidays, skip holidays but let people go the day after a holiday if they could have gone on a holiday - who knows what people will dream up? So I would have some process that reads the definitions and blasts out the schedule start date/time - end date/time records for a lead provider for some date range out into the future.

This could be overkill if the rules are simple, but it sounds like they are reasonably complicated.