X-Received: by 10.224.215.68 with SMTP id hd4mr4571334qab.5.1370729221958; Sat, 08 Jun 2013 15:07:01 -0700 (PDT) X-Received: by 10.50.73.196 with SMTP id n4mr278775igv.3.1370729221836; Sat, 08 Jun 2013 15:07:01 -0700 (PDT) Path: csiph.com!v102.xanadu-bbs.net!xanadu-bbs.net!news.glorb.com!p1no3763282qaj.0!news-out.google.com!y6ni1323qax.0!nntp.google.com!ch1no3217135qab.0!postnews.google.com!glegroupsg2000goo.googlegroups.com!not-for-mail Newsgroups: comp.databases.ms-sqlserver Date: Sat, 8 Jun 2013 15:07:01 -0700 (PDT) In-Reply-To: Complaints-To: groups-abuse@google.com Injection-Info: glegroupsg2000goo.googlegroups.com; posting-host=122.148.159.145; posting-account=PAJwWQoAAAAzb-GyVprLp_ysVWpsQSLk NNTP-Posting-Host: 122.148.159.145 References: User-Agent: G2/1.0 MIME-Version: 1.0 Message-ID: Subject: Re: Table design for Payment Schedule From: craig@listerhome.com Injection-Date: Sat, 08 Jun 2013 22:07:01 +0000 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Xref: csiph.com comp.databases.ms-sqlserver: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 >=20 > "calendar table". Here is just one article about this: >=20 > http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an- >=20 > auxiliary-calendar-table.html >=20 >=20 >=20 > The one thing that strikes me as problematic is how do you identifiy a=20 >=20 > fortnight? Had you had monthly payments, you could use the month on the= =20 >=20 > form YYYYMM. I guess you could use week numbers, but week numbers are=20 >=20 > more difficult to use, and you would either have only second week number, >=20 > or have a row for every week. >=20 >=20 >=20 > For this reason, I would consider having a daily table, and then flags=20 >=20 > for exception days, payments etc. This table could be generated from=20 >=20 > other tables on a nightly basis. >=20 >=20 >=20 > For how far into the future? As long as you need. From what you describe, >=20 > it seems that it should be filled up for the current year and next, but= =20 >=20 > there is nothing to stop you to go on to 2020 if you like. >=20 >=20 >=20 > --=20 >=20 > 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 hopefu= lly 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, i= nstead of a Wednesday. Do you recommend having a row in the table per payme= nt 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 for= tnightly 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.