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

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

Table design for Payment Schedule

Back to article view | Back to comp.databases.ms-sqlserver

Contents

  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

#1513 — Table design for Payment Schedule

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?

[toc] | [next] | [standalone]

#1514

Craig (craig@listerhome.com) writes:
> 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. 

No, it is not odd at all. This is a variation of something known as a
"calendar table". Here is just one article about this:
http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-
auxiliary-calendar-table.html

The one thing that strikes me as problematic is how do you identifiy a 
fortnight? Had you had monthly payments, you could use the month on the 
form YYYYMM. I guess you could use week numbers, but week numbers are 
more difficult to use, and you would either have only second week number,
or have a row for every week.

For this reason, I would consider having a daily table, and then flags 
for exception days, payments etc. This table could be generated from 
other tables on a nightly basis.

For how far into the future? As long as you need. From what you describe,
it seems that it should be filled up for the current year and next, but 
there is nothing to stop you to go on to 2020 if you like.

-- 
Erland Sommarskog, Stockholm, esquel@sommarskog.se

[toc] | [prev] | [next] | [standalone]

#1516

On Saturday, June 8, 2013 5:59:10 PM UTC+10, Erland Sommarskog wrote:
> No, it is not odd at all. This is a variation of something known as a
> 
> "calendar table". Here is just one article about this:
> 
> http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-
> 
> auxiliary-calendar-table.html
> 
> 
> 
> The one thing that strikes me as problematic is how do you identifiy a 
> 
> fortnight? Had you had monthly payments, you could use the month on the 
> 
> form YYYYMM. I guess you could use week numbers, but week numbers are 
> 
> more difficult to use, and you would either have only second week number,
> 
> or have a row for every week.
> 
> 
> 
> For this reason, I would consider having a daily table, and then flags 
> 
> for exception days, payments etc. This table could be generated from 
> 
> other tables on a nightly basis.
> 
> 
> 
> For how far into the future? As long as you need. From what you describe,
> 
> it seems that it should be filled up for the current year and next, but 
> 
> there is nothing to stop you to go on to 2020 if you like.
> 
> 
> 
> -- 
> 
> Erland Sommarskog, Stockholm, esquel@sommarskog.se

Thanks Erland,
That pages leads me to think that maybe a pre-filled table for payments isn't as bad as I thought. The only issues I have with this design, and hopefully you can assist with these, are, what if I generate a table until 2020... and then in 2014, they decide to change the payment days to a Thursday, instead of a Wednesday. Do you recommend having a row in the table per payment date? Or a row per calendar day? I guess if it's per calendar day, then the table is generated once and that's it. But how do I work out where the fortnights are? Have a column added called 'IsPaymentDay' for example, and do an update and see if the day number is divisble by 14 (If they duration is 14)?

And if they change the payment day to another day, or even, change from fortnightly to weekly... just do a recalculation of the payment day, updating rows after the new change comes into effect?

Thanks for the ideas, and hope you can guide further.
Regards,
Craig.

[toc] | [prev] | [next] | [standalone]

#1517

 (craig@listerhome.com) writes:
> That pages leads me to think that maybe a pre-filled table for payments
> isn't as bad as I thought. The only issues I have with this design, and
> hopefully you can assist with these, are, what if I generate a table
> until 2020.... and then in 2014, they decide to change the payment days
> to a Thursday, instead of a Wednesday. Do you recommend having a row in
> the table per payment date? Or a row per calendar day? I guess if it's
> per calendar day, then the table is generated once and that's it. But
> how do I work out where the fortnights are? Have a column added called
> 'IsPaymentDay' for example, and do an update and see if the day number
> is divisble by 14 (If they duration is 14)? 

In the system I work with, there is a schedule for events for pension-
savings accounts. The assets are typically in funds and stocks. Once the 
saver reaches retirement, he gets payments from the account. This is 
arranged by selling off his assets, but only for a year or a month at a 
time. There are three events each month: start of selling, selling close and 
payment date. The users configures the days of the month for these events in 
a table. There is another table that holds non-banking days.

From these tables, there is a job that every night computes the schedule,
leaving days in the past as they are, and up to 2150. (We need to be able
to show when the first payment is due, and for a person in this twenties, 
that could be 40 years from now.)

And that is exactly the idea you should pursue. Somewhere you store the 
definition of the fortnight and also days when there is a lock down. You 
regenerate the table on nightly basis, or triggered by changes to the other 
tables, but only from today and on. It would be a calendar on daily basis, 
as it then can be used for other date calculations as well.

That job may not be too different from the .Net class you originally had 
in mind. The point is that since it is the database, you can use it from
everywhee, including other stored procedures.

-- 
Erland Sommarskog, Stockholm, esquel@sommarskog.se

[toc] | [prev] | [next] | [standalone]

#1515

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
}

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.ms-sqlserver

csiph-web