[Webinar] Learn how to a build a cloud-first strategyRegister Now


Designing Database that required Chart of Accounts

Posted on 2009-04-29
Medium Priority
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
  • 3
  • 2

Accepted Solution

cthulthu earned 1500 total points
ID: 24270737
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

ID: 24279069
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?


Expert Comment

ID: 24283285
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

ID: 24287711
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?


Expert Comment

ID: 24296344
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


Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, we’ll look at how to deploy ProxySQL.
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
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…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

864 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