Chart of Accounts - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

Chart of Accounts

Author

Topic

dewacorp.alliances

452 Posts

Posted - 2009-04-30 : 02:04:00

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:

XX - XXX - XXX - XXX - XXX - XXX - XXX

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

2. Distribution (News)

XX - XXX - XX - XX - XXX

Division - Account - Region - Story - Distribution

3. Projects

XXX - XXXX - XX - XXXX - XX

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?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-30 : 02:26:35

I think 4 tables will do for all cases above (and some more)

Table1 = Centres (CentreID, CentreName) "Manufacturing Services, Distribution, Projects"
Table2 = SegmentTypes (SegmentTypeID, SegmentTypeName) "Company, Accounts, Account, Story"
Table3 = Segments (SegmentID, CentreID, SeqmentTypeID, SegmentCode) "XX, XXX, XXXX"
Table4 = CentreSegmentTypeSequencies (CentreID, SegmentTypeID, Sequence)

E 12°55'05.63"
N 56°04'39.26"

dewacorp.alliances

452 Posts

Posted - 2009-04-30 : 07:49:31

quote:
Originally posted by Peso

I think 4 tables will do for all cases above (and some more)

Table1 = Centres (CentreID, CentreName) "Manufacturing Services, Distribution, Projects"
Table2 = SegmentTypes (SegmentTypeID, SegmentTypeName) "Company, Accounts, Account, Story"
Table3 = Segments (SegmentID, CentreID, SeqmentTypeID, SegmentCode) "XX, XXX, XXXX"
Table4 = CentreSegmentTypeSequencies (CentreID, SegmentTypeID, Sequence)

Hi Peso

I don't think you need the Table1 cause I mentioned above that it's only example that company can have those 3 models but it could be just 5 segments (XXX-xx-XXX-XX-XXX) or just 4 segments.

Looking at your suggestion (I modified a bit):

Table1 = SegmentTypes (SegmentTypeID, SegmentTypeName)
Example:
10, "Company"
11, "Accounts"
12, "Region"
13, "Division"

Table2 = Segments (SegmentID, SeqmentTypeID, SegmentCode, Segment Description)
Exmple:
1, 10, "AA", "Company AA"
2, 11, "70001", "Expenses Mobile Phone"
3, 12, "NSW", "New South Wales"
4, 13, "CRPRTESRVC", "Corporate Service"

Table3 = CentreSegmentTypeSequencies (SegmentTypeID, Sequence)

Not quite sure what this table is for ?!?!

Also ... it's missing the combining of all those segments into the complete Chart of Account:

"AA", "70001", "NSW", "CRPRTESRVC"

When I storing this combination of this COA with cost:

COST; CHARTOFACCOUNT:

$200; "AA-70001-NSW-CRPRTESRVC"

I am storing as whole string (like above) or breakdown into:

COST; CHARTOFACCOUNT; SEGMENT1, SEGMENT2, SEGMENT3, SEGMENT4
$200; "AA-70001-NSW-CRPRTESRVC", "AA", "70001", "NSW", "CRPRTESRVC"

The issue about this approach that you will have a static (preset number of the segment which is 4 BUt I guess I can increase that to 15 which i don't think company will have more than that). But I guess the design it's not clean though.

I am appreciated your comment.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-30 : 08:22:53

The table CentreSegmentTypeSequencies is for knowing which order to concatenate a specific segmenttype.

Is it
NSW-CRPRTESRVC-70001-AA

or
AA-70001-NSW-CRPRTESRVC

or
NSW-AA-70001-CRPRTESRVC

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-30 : 08:24:59

And you should keep your design as normalized as possible.
I did this design for 4 years ago for a worldwide company in Sweden located in Lund, and they still haven't outgrown the design!
Even tough they have added new "dimensions" (segments) to their platform.

E 12°55'05.63"
N 56°04'39.26"

dewacorp.alliances

452 Posts

Posted - 2009-04-30 : 09:04:07

quote:
Originally posted by Peso

And you should keep your design as normalized as possible.
I did this design for 4 years ago for a worldwide company in Sweden located in Lund, and they still haven't outgrown the design!
Even tough they have added new "dimensions" (segments) to their platform.

Thanks for the info Peso. But then how do I store the whole segments in conjuction with the transaction such as cost as per explain above?

COST, CHARGEOFACCOUNT

I think I need to breakdown this into segments when it save the data for the purpose of the roll up report stuff.

Any comment on this?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-30 : 09:58:14

You can have a view for that.
You can even make that view indexed.

E 12°55'05.63"
N 56°04'39.26"

dewacorp.alliances

452 Posts

Posted - 2009-04-30 : 17:22:53

After gathering info bit here and there and looking at this URL:

[url]http://www.accountingsoftware411.com/Press/Insider/InsiderArticleView.aspx?docid=10330&iid=1025[/url], here's the design:

1. ChartAccounts

ChartAccountID INT
TenantID
ChartAccountCode VARCHAR(256)
Dimension1Code VARCHAR(16)
Dimension2Code VARCHAR(16)
Dimension3Code VARCHAR(16)
Dimension4Code VARCHAR(16)
Dimension5Code VARCHAR(16)
Dimension6Code VARCHAR(16)
Dimension7Code VARCHAR(16)
Dimension8Code VARCHAR(16)
IsExpired BIT
ExpiredDate DATETIME

Note: I can see that the Dimension/Segment is limted to only 8 BUT I don't think other company will have more than 8. I am open suggestion with this approach.
We put TenantID because in this application we store for multiple clients/organisation.

Sample Data:

1|310-01-4000|310|01|4000||||||
2|310-02-4000|310|02|4000||||||
3|310-03-4000|310|03|4000||||||
4|320-01-4000|320|01|4000||||||
5|320-02-4000|320|02|4000||||||
6|320-03-4000|320|03|4000||||||
7|200-01-5000|200|01|5000||||||
...

2. Dimensions

DimensionID INT
ParentDimensionID INT
TenantID INT
DimensionTypeID INT
DimensionCode vaARCHAR(16)
DimensionName VARCHA(128)
DimensionDescription VARCHAR(256)

Sample Data:

1|0|1|1|100|CORPORATE CONSOLIDATION|
2|1|1|1|300|MARKETING|
3|2|1|1|310|USA MARKETING|
4|2|1|1|320|EUROPE MARKETING|
5|2|1|1|330|CANADA MARKETING|
6|1|1|1|200|PRODUCTION|
7|1|1|1|900|CORPORATE OVERHEAD|
8|0|1|2|00|ALL LINES|
9|8|1|2|01|LINE 1|
10|8|1|2|02|LINE 2|
11|8|1|2|03|LINE 3|
12|0|1|3|4000|Expense on phone
13|0|1|3|5000|Expense on DATA

3. DimensionTypes

DimensionTypeID INT
TenantID INT
DimensionTypeCode VARCHAR(32)
DimensionTypeName VARCHAR(128)
DimensionTypeDescription VARCHAR(512)
ChartAccountSequence INT
Length INT

Sample Data:

1|1|DEPARTMENT|Department| |1|5
2|1|LINE|Line| |2|5
3|1|NATURALACCOUNT|Natural Account| |3|5

4. TRANSACTION

TransID INT
TransDate DATETIME
PhoneNo VARCHAR(32)
CostDescription VARCHAR(128)
Amount MONEY
ChartAccountID INT

Sample Data:

1|2009-05-01 17:00AM|0290000000|Local Call|30.00|1
2|2009-05-01 18:01AM|0390000000|Local Call|25.50|2
...

Any thought about this? Especially for the ChartAccount table? As you can see it sets to 8 (Dimension8Code) which is that is the downside of this. But I don't think company will have many dimension? Within the dimension table itself it has DimensionID/ParentDimensionID relationship to cater the structure within the dimension itself. Or do you have a better idea?

I am appreciated your comment. Again this is not an accounting system .. it's just a simple allocation cost using the chart of account.

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources