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

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

Re: Table design for Payment Schedule

Show all headers | View raw

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.

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