Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #1513

Table design for Payment Schedule

Show all headers | View raw

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?

Back to comp.databases.ms-sqlserver | Previous | Next — Next in thread | Find similar

Thread

Table design for Payment Schedule Craig <craig@listerhome.com> - 2013-06-07 20:48 -0700
  Re: Table design for Payment Schedule Erland Sommarskog <esquel@sommarskog.se> - 2013-06-08 09:59 +0200
    Re: Table design for Payment Schedule craig@listerhome.com - 2013-06-08 15:07 -0700
      Re: Table design for Payment Schedule Erland Sommarskog <esquel@sommarskog.se> - 2013-06-09 11:04 +0200
  Re: Table design for Payment Schedule bradbury9 <ray.bradbury9@gmail.com> - 2013-06-08 03:54 -0700

csiph-web