Database Design Input

Question

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

Here is the proposed design

1. ChartAccounts

ChartAccountID INT
TenantID INT
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 limited to only 8 BUT I don't think any other company will have more than 8. I am open to 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 VARCHAR(16)
DimensionName VARCHAR(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 thoughts 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 any company will have that many dimensions?

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 appreciate your comments. Again this is not an accounting system .. it's just a simple allocation cost using the chart of account.

Answer 1

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.

Smells bad. Make a one-to-many relation to a table of dimension_segments.

Or maybe it's a many-to-many between chartAccounts and dimensions, since I don't know what a "dimension" is.

Better yet, why not explain what you're trying to model; it's hard to advise you on whether the model works, without knowing what it's supposed to be a model of.

Answer 2

anytime you see fields like this: Dimension1Code VARCHAR(16) Dimension2Code VARCHAR(16) Dimension3Code VARCHAR(16) Dimension4Code VARCHAR(16) Dimension5Code VARCHAR(16) Dimension6Code VARCHAR(16) Dimension7Code VARCHAR(16) Dimension8Code VARCHAR(16)

You need a related table instead. Trust me this will be anightmare to query and maintain.