title: Table design for Payment Schedule
body: <p>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. </p>
  
  <p>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.</p>
  
  <p>So, after the first payment, the person gets $140 sent to them. </p>
  
  <p>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.</p>
  
  <p>Example:</p>
  
  <p>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.</p>
  
  <p>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. </p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>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?</p>
  
  <p>Here's a basic start to what I would LIKE to do.. but... not sure if it can handle the requirements:</p>
  
  <p>This table holds the line for the schedule. Every time there is a change, a new row is added.</p>
  
  <pre><code>CREATE TABLE [dbo].[PaymentSchedule] (
      [PaymentScheduleId] INT  IDENTITY (1, 1) NOT NULL,
      [EffectiveDate]     DATE NOT NULL,
      [EffectiveDays]     INT  NOT NULL,
      CONSTRAINT [pk_PaymentSchedule] PRIMARY KEY CLUSTERED ([PaymentScheduleId] ASC)
  );
  </code></pre>
  
  <p>This table holds the exception dates. So, a single day will have the same start and end date... A range will have a differing start and end date.</p>
  
  <pre><code>CREATE TABLE [dbo].[ExceptionDate]
  (
      [ExceptionDateId] INT NOT NULL IDENTITY, 
      [StartDate] DATE NOT NULL, 
      [EndDate] DATE NOT NULL, 
      [Description] VARCHAR(50) NOT NULL, 
      CONSTRAINT [PK_ExceptionDate] PRIMARY KEY ([ExceptionDateId]) 
  )
  </code></pre>
  
  <p>And this could hold a history of payments, linked to the schedule that was used. </p>
  
  <pre><code>CREATE TABLE [dbo].[PaymentScheduleHistory] (
      [PaymentScheduleHistoryId] INT IDENTITY (1, 1) NOT NULL,
      [PaymentScheduleId]        INT NOT NULL,
      CONSTRAINT [pk_PaymentScheduleHistory] PRIMARY KEY CLUSTERED ([PaymentScheduleHistoryId] ASC),
      CONSTRAINT [fk_PaymentScheduleHistory_PaymentSchedule] FOREIGN KEY ([PaymentScheduleId]) REFERENCES [dbo].[PaymentSchedule] ([PaymentScheduleId])
  );
  </code></pre>
  
  <p>So, based on these early table designs... I'm not sure how to get it to work. I need to somehow work out if todays date is a trigger date, based on the schedule. Once that's working, I can add functionality to manage exception dates. Does this seem to be on the right track?</p>