Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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?

share|improve this question
add comment

3 Answers

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)

share|improve this answer
    
I'm confused by your Schedule table. I don't see how start/end dates and times fits with rules like "Sunday @ 8AM - Friday @ 3PM -- all hours in between". If you're using actual Dates (eg: 1970-01-01) you would need a lot of repeated data to make this work and it would be a nightmare to maintain. Or for dates, do you mean days of the week? Maybe I'm not understanding your solution? –  Cyrena Jun 6 '11 at 21:55
    
Cyrena, I had the same question. However, what I could do is layout schedules based on May 1 - May 7 (I just chose that week because it seems easy -- 1=Sunday, etc). Then when loading the schedule, I could just use the time and the Weekday portions of the value out of the DB. Exceptions could (and would) still be fully qualified dates. –  Matt Dawdy Jun 7 '11 at 3:18
    
I've never been too concerned about too many records in a table, when you're talking about maybe a couple hundred or maybe a thousand records that look similar; however, if you're concerned about that issue, you could add another table called "ScheduleType", which could represent the different types of schedules you can have. Then the Schedule table would have a foreign key reference to ScheduleType, and that would give you a little more flexibility to handle different types of schedules. At that point, the User interface and the Business Logic ensures the data is valid and meaningful. –  Tim Claason Jun 7 '11 at 13:11
add comment

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.

share|improve this answer
1  
Clients already have their own set of credentials, and I validate this against the originating IP address and a few other things. This is as secure as we need to get. More often than not, the problem with them sending things off schedule is that their own code isn't setup to handle blocked times very well. I don't think it's malicious, merely forgetful if you will. –  Matt Dawdy Jun 7 '11 at 19:06
add comment

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.

share|improve this answer
    
You brought up 2 things I love -- first, "the logic to tell if a given time falls in a schedule probably won't" and also the thing about generating an actual dated schedule from rules, only X days in advance. At least that's how I read it. –  Matt Dawdy Oct 12 '11 at 3:35
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.