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

A virtual Date table

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

  • This topic has 4 replies, 2 voices, and was last updated 8 years, 9 months ago by jaska_lee.
Viewing 5 posts - 1 through 5 (of 5 total)
  • Author
    Posts
  • September 8, 2014 at 5:26 am #789
    jaska_lee
    Participant

    Is it possible to build a virtual date table or view with which we can set filters on and iterate through? This table or view does not necessarily need to physically store every date but dynamically self-generated in memory. We can then do all kinds of cumulative calculations against this date table, without creating lots of indexs with cumulative totals.

    September 8, 2014 at 3:06 pm #1101
    amckinlay
    Keymaster

    Sorry, I do not understand. Can you give examples of how this would be used?

    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.

    September 8, 2014 at 5:08 pm #1103
    amckinlay
    Keymaster

    Ah, I see what you mean. I have not seen that before. It is an interesting idea.

    Unfortunately, Suneido does not currently have any way to do that.

    Internally (in the C++ and Java code) it would not be too hard to add. Basically it would require get next/prev and select methods.

    September 9, 2014 at 2:07 am #1104
    jaska_lee
    Participant

    There are pros and cons for both of them:

    pros:
    1) the first approach is fast and easy during calculation, as it pulls out the totals which are physically stored.
    2) the second approach does not have any physical impact to the database or cause any additional operations. And in real cases, the requirements are specific, like for a certain number of G/L Account in a given period of time. These could help to narrow down the transactions which can be profitable in reality.

    cons:
    1) the first approach is costly to maintain during transaction update. And it has a physical, permanent impact to the database and front end system.
    2) the second approach needs to calculate every result from the transactions every time it is needed, which could be very large and slow too.

    Never mind, I just thought it’s an idea worth a look.

  • Author
    Posts
Viewing 5 posts - 1 through 5 (of 5 total)
  • You must be logged in to reply to this topic.
Log In

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