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

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

Re: Table design for Payment Schedule

Show all headers | View raw

El sábado, 8 de junio de 2013 05:48:04 UTC+2, Craig  escribió:
> 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?

At job I did somehing similar. The easiest way if dealing with windows environments and .NET *in my personal opinion* is create a simple console application that is schedulled to run daily that gets the data from the sql server database and does the payment.

In the .exe.config file I would put the days it should usually trigger the payments. <add key='dayToPay' value='14' />
In the database I would just introduce in the exceptions table the following format. Both field should make the primary key

InitialExceptionDate(DateTime)    -    EndExceptionDate(DateTime)


The .NET console application should check if it should do the payment considering:

DateTime.DayOfWeek property to check for weekends
The Sql Server exceptions table (aka holidays table)

That way you can avoid a schedule table and you just should store the data at the beginning of the year when the yearly company calendar is released, or when changes to the holidays happen.




Quick sample code, untested (dont have visual studio handy), missing of propper try-catch-finally code to make sure connection to database is always closed (quite in a hurry at the moment):

// Get the payDay of the current month
DateTime dayToPay = new DateTime(DateTime.Today.Year, DateTime.Today.Month, Int32.Parse(ConfigurationManager.AppSettings["dayToPay"]));

SqlConnection conn = new SqlConnection("connection string to database");
SqlCommand comm = new SqlCommand("select InitialExceptionDate from Holidays where @dayToPay between InitialExceptionDate and EndExceptionDate", conn);
comm.Parameters.AddWithValue("@dayToPay", dayToPay);
conn.Open();
object firstHolidayDay = comm.ExecuteScalar();
conn.Close();

bool isInHoliday = (firstHolidayDay == null || > firstHolidayDay == DBNull.Value);

// if in holidays get first non-holiday day
if(isInHoliday) dayToPay = ((DateTime) firstHolidayDay).AddDays(-1);

// check if dayToPay is still laboral day
if(dayToPay.DayOfWeek == DayOfWeek.Sunday) dayToPay = dayToPay.AddDays(-2);
if(dayToPay.DayOfWeek == DayOfWeek.Saturday) dayToPay = dayToPay.AddDays(-1);

if(dayToPay == DateTime.Today) {
 // Do the payment code
}

Back to comp.databases.ms-sqlserver | Previous | Next — Previous 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