Designing Database that required Chart of Accounts

Posted on 2009-04-29
Last Modified: 2013-11-12
Hi there

I am designing the app which required the Chart of Accounts (finance cost centre). I want to basically to have a flexibility for any business models.

So far I found that any business models (industry) can have:

1. Manufacturing Services:


Company - Cost Centre - Account - Product - Product Line - Sub Account

2. Distribution (News)

XX - XXX - XX - XX - XXX

Division - Account - Region - Story - Distribution

3. Projects


Company - Accounts - Department - Projects - Project Type

My understanding that each XX can be considered as SEGMENTS.

Couple approaches:

1. I can have tables which containing 15 SEGEMENTS .... the assumption I won't the company has more than 15 SEGMENTS.

2. Or make it dynamically which is having ParentID/ID relationshi into a single SEGMENT table.

Some consideration ... in the transaction table I will record the full code of segment for instance AAA-123-XXX-123-70303. Also, considering for reporting, do the roll up report for instance for company/division/business units.

This is not accounting software though ... it's just a billing but require chart of account.

Any thoughts?
Question by:dewacorp_alliances
    LVL 2

    Accepted Solution

    Your idea of putting the segments into a child table sounds like the best solution.  You get flexibility with the number of segments that can be divided; you also save space in your tables.  Additionally, you can define attributes for each segment:  length, effective / expiration date, name, order in the full COA sequence, status, etc.  The downside is that the process of reading / writing the segments in the COA becomes more complcated.

    Author Comment

    Hi Cthulthu

    My question how do link this into my transaction table though cause I have a PHONENO, COST and CHARGE_OF_ACCOUNT.

    For CHARGE_OF_ACCOUNT, do  I store to split this by segements OR whole string?

    LVL 2

    Expert Comment

    You would store it as the entire string (with or without segment indicators / dashes).  If you allowed history or modifications of the account structure, you would also need an effective date in your transaction table that would serve to locate the segment information that was in use at the time of the transaction.  Pulling out the account string into its components could be made easier by also storing the start / end point of the each segment of an account within your 1-M table.

    Author Comment

    Hi chtulthu

    Thanks for this. I guess you right I need in the transaction table: PHONENO, COST, CHARGEOFACCOUNT, TRANSCTIONDATE

    For this statement: "Pulling out the account string into its components could be made easier by also storing the start / end point of the each segment of an account within your 1-M table."

    Not quite sure what do you mean by this? Is this a sequence of the SEGMENT of CHARTOFACCOUNT?

    LVL 2

    Expert Comment

    Yes, it's an indicator of where things are in your account sequence.  So, for instance, if your COA is stored in the transaction table as

    and it broke down to:

    Company - Cost Centre - Account - Product - Product Line - Sub Account

    Your entries in the SEGMENTS table might look as shown in the code snippet.  Then you could use something like MID or SUBSTR to pull out the sections from the string stored in the TRANSACTS table based on the SEGSTART / SEGEND values (know in advance whether you're going to store separators).

    SegName   SegPos   SegStart   SegEnd
    COMPANY     1          1        2
    ACCOUNT     3         11       13
    COSTCTR     2          4        9
    PRODUCT     4         15       17
    PRODLINE    5         19       21
    SUBACCT     6         23       24

    Open in new window


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Suggested Solutions

    Title # Comments Views Activity
    MySQL going to sleep 4 56
    .csv files 5 62
    Folder Replication 4 35
    Required field data in layout in Filemaker Pro 10 6 22
    Introduction This question got me thinking... ( Why shouldn't we use Globals? This is a simple question without a simple answer.  How do you explain these concepts to a programmer w…
    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now