Suneido

Integrated Application Platform

  • Home
  • Learning
    • Suneido Manual
    • Beginners
      • Inside Suneido
      • The Suneido Programming Language
      • The Suneido Database
      • Installing Suneido
      • Building Suneido
      • IDE Go To Tour
      • Upgrading To A New Release
    • Advanced
      • Canvas Control
      • DrawControl Part 1
      • DrawControl Part 2
      • DrawControl Part 3
      • SAX Like XML Processing
      • A Plug-In Architecture
      • A Simple Wizard Framework
      • An HTML Include Facility
      • An RSS 2 Feed Creator
      • MIME Generation
      • A New Add-on Facility
      • Workspace Improvement Hack
    • Mockito for Suneido
    • The Suneido Task Scheduler
    • Contributing To Suneido
    • Contributor Assignment of Copyright
    • Language Translation
    • Future Directions
    • Interview With Andrew Mckinlay
  • Forum
    • Announcements
    • Internals & Enhancements
    • Cookbook
    • General
  • FAQ
  • Screenshots
  • Downloads
  • Links

Re: Re: A virtual Date table

Integrated Application Platform › Forums › General › A virtual Date table › Re: Re: A virtual Date table

September 8, 2014 at 4:44 pm #1102
jaska_lee
Participant

sorry for not being specific.

There are cases in which I need to do cumulative totals at each date for a given period of time. For instance, G/L Account balance at each date through out the entire fiscal year. Or, Item inventory at each date for a given period of time. The most common approach I have seen in many different systems is to create a kind of index table which consists of concerned elements, e.g.

"GL Account No."	Date			Balance
1100 09/08/2014 10
1100 09/09/2014 20
1100 09/10/2014 30
....
1200 09/08/2014 10
1200 09/09/2014 20
1200 09/10/2014 30
....

Whenever there is transaction happening to G/L Account No, we try to maintain this index sum table. So that we can pull it out later for cumulative balance reporting. The same logic could be implemented for other similar requirements. The problem with this approach is that by the time it goes, this table becomes increasingly large and very “expensive” to maintain. Sometimes, it gets so bad that the system becomes really slow and we cant afford to maintain this table during transaction update. And, technically, it can be quite challenging to write the code to maintain this table, especially when the criterio becomes complex.

There is a different approach which I have seen in another system that is written in C++. It implements a kind of virtual date table like a built-in date calender which has a table structure like this:

Date			Name
01/22/0001 Monday
01/23/0001 Tuesday
01/24/0001 Wednesday
01/25/0001 Thursday
01/26/0001 Friday
01/27/0001 Saturday
01/28/0001 Sunday
...
01/01/9998
01/01/9999

The data in this table is not physically stored in the database but dynamically populated in memory. In this case, if I want to calculate G/L Balance at date, I simply do a G/L Account left join Date, then using this G/L Account No. and each specific date, one can easily filter out the transactions and calculate the totals accordingly. This way, there is no need to create or maintain any additional tables with totals. And it is easy to change the logic of calculation in case criterio changes without physically affecting the database, in fact, with Rules, one can just create as many derived columns as they need against with this Date table and attach them on fly for different needs.

Search Forums

Log In
Welcome to the new Suneido Web Site and Forum.
Register
Lost Password
users of the previous PHPBB Forum should be able to log in with their previous username and password.

Recent Topics

  • Alpha copy of gSuneido to play with
  • how to start jsuneido ?
  • Problem Starting Suneido…
  • Dialog not showing buttons
  • New link for Suneidojo

Contact Us | Legal Statement | Privacy Statement | SiteMap

Copyright © 2023 Axon® Development Corporation. All Rights Reserved. - Open Source Integrated Database and Programming Language