X-Received: by 10.224.59.205 with SMTP id m13mr3344441qah.7.1370688845039; Sat, 08 Jun 2013 03:54:05 -0700 (PDT) X-Received: by 10.49.87.232 with SMTP id bb8mr131149qeb.28.1370688845021; Sat, 08 Jun 2013 03:54:05 -0700 (PDT) Path: csiph.com!v102.xanadu-bbs.net!xanadu-bbs.net!news.glorb.com!p1no3706147qaj.0!news-out.google.com!y6ni1323qax.0!nntp.google.com!ch1no3156643qab.0!postnews.google.com!glegroupsg2000goo.googlegroups.com!not-for-mail Newsgroups: comp.databases.ms-sqlserver Date: Sat, 8 Jun 2013 03:54:04 -0700 (PDT) In-Reply-To: Complaints-To: groups-abuse@google.com Injection-Info: glegroupsg2000goo.googlegroups.com; posting-host=87.222.80.254; posting-account=jIl9nQoAAAA9P37IIqQq64yAx-2NQYiu NNTP-Posting-Host: 87.222.80.254 References: User-Agent: G2/1.0 MIME-Version: 1.0 Message-ID: <50b2893f-e51f-4a46-890a-e9e5ffe6dee3@googlegroups.com> Subject: Re: Table design for Payment Schedule From: bradbury9 Injection-Date: Sat, 08 Jun 2013 10:54:05 +0000 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Xref: csiph.com comp.databases.ms-sqlserver:1515 El s=E1bado, 8 de junio de 2013 05:48:04 UTC+2, Craig escribi=F3: > 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, = the 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= payments and send them out. >=20 >=20 >=20 > So, an example. PersonA gets $10/day. Every two weeks, the system accumul= ates all payments for a person, sums them up, and sends out a payment. >=20 >=20 >=20 > So, after the first payment, the person gets $140 sent to them. >=20 >=20 >=20 > However, it's complicated by the way that, if the payment day falls on a = public holiday, or a shut down period, such as the christmas leave period (= 24th Dec to 2nd Jan), the system must make the payment on the day before th= e public holiday, or shut down period, and process any future payments for = that period. >=20 >=20 >=20 > Example: >=20 >=20 >=20 > Payment period is 1st June, to 14th June. The payments for this period wi= ll be sent on the 14th, and be an accumulation of all payments for the peri= od... So, on the 14th, we do a payment run that collects all payments withi= n that period, and sends it off. However, there is a company shut down peri= od from the 10th until the 18th. So, on the 9th, the system must get all pa= yments from the 1st, to the 14th (The building of these payments has been h= andled), and create the payment on the 9th. The next period (15th to the 29= th) will work as normal. >=20 >=20 >=20 > 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 Dat= e =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... tha= t seems odd to me. I thought that we just need one Schedule row, with a 'Ef= fective Date' of the 1st of June, and a 'Duration' which say 'Fortnight', o= r something. Then, with .Net, or SQL, we can work out if we're on a 'trigge= r' date. So, the first trigger date would be effective date plus 14 days. I= f 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 ac= tive, and when the system date hits that date, the new schedule becomes act= ive. There would have to be some logic to handle this, but the basic concep= t of one row in the schedule date is my idea. Holding all future fortnight = dates seems - odd. >=20 >=20 >=20 > Handling close down periods would be handled via an 'Exception Dates', ty= pe table, which simply holds public holidays and shut down periods, Basical= ly, dates where payments can't be processed. >=20 >=20 >=20 > So, every night, a process runs. And takes the active schedule row, and w= orks out if we're on fortnight date matching that effective date (How?), an= d checks the exception table to work out if we need to trigger an early pay= ment. >=20 >=20 >=20 > How we work out if we're on a payment date based on an Effective date, an= d then checking the 'period' may be tricky... But, surely storing all futur= e fortnights is - wrong? At job I did somehing similar. The easiest way if dealing with windows envi= ronments and .NET *in my personal opinion* is create a simple console appli= cation that is schedulled to run daily that gets the data from the sql serv= er database and does the payment. In the .exe.config file I would put the days it should usually trigger the = payments. In the database I would just introduce in the exceptions table the followin= g format. Both field should make the primary key InitialExceptionDate(DateTime) - EndExceptionDate(DateTime) The .NET console application should check if it should do the payment consi= dering: DateTime.DayOfWeek property to check for weekends The Sql Server exceptions table (aka holidays table) That way you can avoid a schedule table and you just should store the data = at the beginning of the year when the yearly company calendar is released, = or when changes to the holidays happen. Quick sample code, untested (dont have visual studio handy), missing of pro= pper try-catch-finally code to make sure connection to database is always c= losed (quite in a hurry at the moment): // Get the payDay of the current month DateTime dayToPay =3D new DateTime(DateTime.Today.Year, DateTime.Today.Mont= h, Int32.Parse(ConfigurationManager.AppSettings["dayToPay"])); SqlConnection conn =3D new SqlConnection("connection string to database"); SqlCommand comm =3D new SqlCommand("select InitialExceptionDate from Holida= ys where @dayToPay between InitialExceptionDate and EndExceptionDate", conn= ); comm.Parameters.AddWithValue("@dayToPay", dayToPay); conn.Open(); object firstHolidayDay =3D comm.ExecuteScalar(); conn.Close(); bool isInHoliday =3D (firstHolidayDay =3D=3D null || > firstHolidayDay =3D= =3D DBNull.Value); // if in holidays get first non-holiday day if(isInHoliday) dayToPay =3D ((DateTime) firstHolidayDay).AddDays(-1); // check if dayToPay is still laboral day if(dayToPay.DayOfWeek =3D=3D DayOfWeek.Sunday) dayToPay =3D dayToPay.AddDay= s(-2); if(dayToPay.DayOfWeek =3D=3D DayOfWeek.Saturday) dayToPay =3D dayToPay.AddD= ays(-1); if(dayToPay =3D=3D DateTime.Today) { // Do the payment code }