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

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

Re: Table design for Payment Schedule

Show all headers | View raw

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

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