From: craig@listerhome.com   
      
   We are developing a database that handles payments to people. The area I am   
   focusing on at the moment, is the scheduled payment options. Basically, the   
   system sends payments to people once every fortnight. So, every 14 days, on a   
   specific day (Wednesday,    
   at the moment), the system must process the payments and send them out.   
      
   So, an example. PersonA gets $10/day. Every two weeks, the system accumulates   
   all payments for a person, sums them up, and sends out a payment.   
      
   So, after the first payment, the person gets $140 sent to them.   
      
   However, it's complicated by the way that, if the payment day falls on a   
   public holiday, or a shut down period, such as the christmas leave period   
   (24th Dec to 2nd Jan), the system must make the payment on the day before the   
   public holiday, or shut down    
   period, and process any future payments for that period.   
      
   Example:   
      
   Payment period is 1st June, to 14th June. The payments for this period will be   
   sent on the 14th, and be an accumulation of all payments for the period... So,   
   on the 14th, we do a payment run that collects all payments within that   
   period, and sends it off.   
    However, there is a company shut down period from the 10th until the 18th.   
   So, on the 9th, the system must get all payments from the 1st, to the 14th   
   (The building of these payments has been handled), and create the payment on   
   the 9th. The next period (   
   15th to the 29th) will work as normal.   
      
   The question is: Handling this payment schedule. It's been proposed that we   
   create a schedule table, with all the periods. So, record one: Start Date =   
   1st June, End Date, 14th June. Record two has Start as the 15th, end of the   
   29th.. and so on. How far    
   to go into the future? No idea. But... that seems odd to me. I thought that we   
   just need one Schedule row, with a 'Effective Date' of the 1st of June, and a   
   'Duration' which say 'Fortnight', or something. Then, with .Net, or SQL, we   
   can work out if we're    
   on a 'trigger' date. So, the first trigger date would be effective date plus   
   14 days. If they then decided to change it from a Wednesday to a Monday (For   
   example), add a new Schedule row, with the Monday date that the schedule   
   becomes active, and when    
   the system date hits that date, the new schedule becomes active. There would   
   have to be some logic to handle this, but the basic concept of one row in the   
   schedule date is my idea. Holding all future fortnight dates seems - odd.   
      
   Handling close down periods would be handled via an 'Exception Dates', type   
   table, which simply holds public holidays and shut down periods, Basically,   
   dates where payments can't be processed.   
      
   So, every night, a process runs. And takes the active schedule row, and works   
   out if we're on fortnight date matching that effective date (How?), and checks   
   the exception table to work out if we need to trigger an early payment.   
      
   How we work out if we're on a payment date based on an Effective date, and   
   then checking the 'period' may be tricky... But, surely storing all future   
   fortnights is - wrong?   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)