X-Received: by 10.224.53.198 with SMTP id n6mr2689047qag.2.1370663284870; Fri, 07 Jun 2013 20:48:04 -0700 (PDT) X-Received: by 10.50.147.98 with SMTP id tj2mr44132igb.10.1370663284651; Fri, 07 Jun 2013 20:48:04 -0700 (PDT) Path: csiph.com!v102.xanadu-bbs.net!xanadu-bbs.net!news.glorb.com!ch1no3118118qab.0!news-out.google.com!y6ni1260qax.0!nntp.google.com!ch1no3118117qab.0!postnews.google.com!glegroupsg2000goo.googlegroups.com!not-for-mail Newsgroups: comp.databases.ms-sqlserver Date: Fri, 7 Jun 2013 20:48:04 -0700 (PDT) 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 User-Agent: G2/1.0 MIME-Version: 1.0 Message-ID: Subject: Table design for Payment Schedule From: Craig Injection-Date: Sat, 08 Jun 2013 03:48:04 +0000 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Xref: csiph.com comp.databases.ms-sqlserver:1513 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, th= e 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 p= ayments and send them out. So, an example. PersonA gets $10/day. Every two weeks, the system accumulat= es 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 pu= blic holiday, or a shut down period, such as the christmas leave period (24= th 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 th= at 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 paym= ents from the 1st, to the 14th (The building of these payments has been han= dled), 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 = =3D 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 'Effe= ctive 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 acti= ve, and when the system date hits that date, the new schedule becomes activ= e. 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 da= tes 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 wor= ks 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 payme= nt. 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?