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

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

Re: Table design for Payment Schedule

Show all headers | View raw

 (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

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