Database login
After gathering information a bit here and there and looking at this url :
Here is the suggested 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 see that the size / segment is only limited to 8, but I don't think any other company will have more than 8. I'm open to suggesting with this approach. We are putting the TenantID because we are storing multiple clients / organization in this application.
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 on this? Specifically for the ChartAccount table? As you can see, it sets the value to 8 (Dimension8Code), which is the downside of this. But I don't think any company will have many dimensions?
In the dimension table itself, it has a DimensionID / ParentDimensionID relationship to serve the structure in the dimension itself. Or do you have an idea?
I appreciate your comments. Again, this is not an accounting system. It's just a simple allocation cost using the chart of accounts.
source to share
Note. I see that the size / segment is limited to 8, but I don't think the other company will have more than 8. I open a proposal with this approach.
Smells badly. Make a one-to-many relationship to the segment table.
Or maybe it's a lot-to-many between chartAccounts and dimensions, since I don't know what a “dimension” is.
Better yet, why not explain what you are trying to model? you find it difficult to advise whether a model works without knowing that it should be a model.
source to share
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) VARCHARCode (16) Dimension8
Instead, you need a linked table. Trust me this will be a challenge for request and support.
source to share