|
|
I am trying to build a Double Entry Accounting System from scratch.
I will use SQL Server Express 2014 for the database but I am totally confused about the optimal table structure.
There will be four principle account types namely Assets, Liabilities, Income and Expenses
Each transaction will have an effect on 2 accounts and both accounts can belong to same or different principal accounts.
I am seeking help for where to start and how much tables should be there?
My present design is like this:
1. Accounts Table
a.AccountID
b.AccountName
c.AccountType(which can be any of four or and sub account created under them)
d.TransactionID
2.Transactions Table
a.TransactionId
b.TransactionDetailsID
b.AccountID
c.TranDate
d.TranDrAmount
e.TranCrAmount
3. TransactionDetails Table
a.TransactionDetailsID
b.ItemName
c.ItemQuantity
d.ItemRate
e.ItemAmount
f.TransactionType (can be sale, purchase, receipt etc.)
Is this table structure good or it should be like something else?
|
|
|
|
|
Keys are wrong: 1d. remove TransactionID
Combine tables 2 and 3 into one ... you'll thank me later.
Add a "reference field" or 2 to the transaction for: invoice numbers; check #; P.O. #; etc.
"Quantities" are not part of an "accounting system" unless you are posting to a "statistical account" ... Not advised; that's what Inventory systems are for.
Accounts and transactions / postings: that's all you need for the operational system ... Everything else is informational.
|
|
|
|
|
Thanks for your reply.
1. I am planning to add the inventory master to that structure in future.
2. After combining tables 2 and 3, can we manage multiple transactions in 1 invoice?
Consider a scenario:
I have a client on whom I will make an invoice for services provided.
Now I have provided 3 services to him Namely
a. Bookkeeping Service
b. Reports Preparation Service
c. Business Consultancy Service
The Invoice will be having:
1. Date
2. Invoice Number
3. Client Name (which will be and Asset Account)
4. The Line level items (which are the services provided to him)
S.No | Item/Service Name | Duration | Rate per duration | Amount
1 | Bookkeeping Service | 1 year | $1000 | $1000
2 | Reports Prep Service| 6 months | $100 | $600
3 | Busin. Conslt. Srv. | 1 month | $500 | $500
Total : $1600.00
Applicable Taxes (if any): $0.00
Grand Total: $1600.00
Now each line level item will have an independent account mapped to it which will be credited and the client account will be credited.
On receipt of that invoice amount, Cash Account will be debited and Client account will be credited.
I need to allocate each receipt to an invoice as well as the client and calculating the closing balance.
I am a bit confused about making this design scale-able to accommodate new tables like inventory, payroll, taxation etc.
|
|
|
|
|
What you were modelling earlier was the "General Ledger": e.g.
[Account]->>[Trans]
What you are now getting into are the "Journals" (Accounts Receivable; Accounts Payable; etc.): e.g.
[Document]->>[Journal Entries]
(where a "document" can represent an Invoice, Sale (on credit or cash), Purchase Order, etc.)
The "document" contains "balanced" journal entries which are subsequently "posted" to the "General Ledger" (as "Trans").
(As I said before, consider what is "accounting data" and what is "tramp data" that belongs in its own subsystem; e.g. Payroll / personal; inventory "stats" versus $).
|
|
|
|
|
I get it now, I was quite confused earlier.
Basically what I need is the Invoice > Journal with ability to view Account Balances
What will be the optimal data structure to achieve that?
Should I keep only 2 tables? i.e.
1. Accounts
2. Transactions
Gerry Schmitz wrote: What you were modelling earlier was the "General Ledger": e.g.
[Account]->>[Trans]
What you are now getting into are the "Journals" (Accounts Receivable; Accounts Payable; etc.): e.g.
[Document]->>[Journal Entries]
(where a "document" can represent an Invoice, Sale (on credit or cash), Purchase Order, etc.)
The "document" contains "balanced" journal entries which are subsequently "posted" to the "General Ledger" (as "Trans").
(As I said before, consider what is "accounting data" and what is "tramp data" that belongs in its own subsystem; e.g. Payroll / personal; inventory "stats" versus $).
|
|
if (document.all||window.getSelection) {
document.write(unescape("%3Cbutton%20class=%22button%22%20onclick%3D%22return%20QuoteText%28%27"));
document.write("Gerry Schmitz");
document.write(unescape("%27%29%3B%22%20class%3D%22button%22%3E%20%20Quote%20Selected%20Text"));
document.write(unescape("%20%20%3C/button%3E"));
}
Quote Selected Text
|
|
|
|
|
|
The structure depends on your requirements.
2 tables are “optimal” if they meet all YOUR requirements and contain no redundant data; otherwise, it is not (optimal).
It is for you to decide. I presented possibilities.
|
|
|
|
|
I have done such work before. I agree with Prateek that combining table 2 and 3 is better. It results in de-normalised tables but the benefits you get in reporting are huge.
Plus I would strongly recommend that you have only one amount not TranDrAmount and TranCrAmount and make Credits positive and Debits negative. This makes calculating the net balance for an account (which is done very frequently) just a simple SUM statement on Table 2. If negative is is a debit balance and if it is positive it is a credit balance.
HTH
|
|
|
|
|
Whatever you do, make sure you have three distinct databases:
One for yourself,
One for your business partner(s), and
One for the Infernal Revenue Service.
That is the way to succeed in business!
|
|
|
|
|
Hello!
I am a UX Designer for a firm based out of Tampa Florida. We design software for engineering firms as well as architect firms. Currently, we are working on ERP software. You know, that boring stuff whee you fill out timesheets and manage projects.
Anyhoo....
We are trying to make user friendly applications, which means your opinion matters most to me.
The issue I have currently is this: as an architect working at your firm, if you were to log in to a dashboard for a business system, what would you like to see on your dashboard? Things assigned to you, performance items, to do lists, number of vacation days left... the sky is the limit.
Any serious feedback would help tremendously.
Thanks!
|
|
|
|
|
I would think looming milestones in projects where I was a stakeholder would be a biggie, but then I'm not an architect.
"There are three kinds of lies: lies, damned lies and statistics."
- Benjamin Disraeli
|
|
|
|
|
Architect or not, this is the exact feedback I am looking for! Thank you for responding - any other ideas would be welcome as well!
|
|
|
|
|
They're called KPI's (key performance indicators), and will vary by individual and circumstance; and in how they are "visualized" (e.g. gauge; slider; meter; diode; etc.)
For example, my KPI's this afternoon are: Temperature; Precipitation; and Wind Velocity.
(I'm walking the dog).
|
|
|
|
|
Gerry Schmitz wrote: will vary by individual and circumstance
Fully agreed. I tried to point this more out in my own post below. I feel like the UX dude is forced to do the Bussiness Analyst's job in this very example.
|
|
|
|
|
While you question is interesting, the answer from me is...
it depends.
It depends on ones preferences, experience, and focus. There isn't *one* type of architect. There are a lot of people, working as architects.
And while you *could* go out and ask every single one of them, you shouldn't. UX is important, but what you're asking us is something that should be answered by a business analyst working on your project and in the end be sealed into requirements.
UX gets into the game once you display the stuff the BA requested, and not replace the BA's job.
|
|
|
|
|
Why are you asking a bunch of software geeks what your users want, ask the bloody users!
Half the time they have no firm idea but should put up some suggestions. If they are too useless then give them a blank page! Seriously we can't design a dashboard for you, it has to come from your users!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Sorry - I was under the impression this was in an architects forum. I was, indeed, trying to ask the users.
We have a full system with full requirements from a BA. Trust me when I say, I do not want that job. However, often times, the BA requirements are coming from the business, which is fine, but in our case, is often disconnected from what the users want to see.
But thank you all, this has been helpful, and your feedback (coupled with feedback gained from other boards, and some of our actual users) we have what we need to begin.
Thanks again!
|
|
|
|
|
Member 12507295 wrote: what would you like to see on your dashboard
Nothing...
Skipper: We'll fix it.
Alex: Fix it? How you gonna fix this?
Skipper: Grit, spit and a whole lotta duct tape.
|
|
|
|
|
Hi all,
I have to create an integration layer between WCF XML/SOAP and JSON/Rest
Mostly i have to get WCF and send Json/REST, from LOB apps (all over WCF) to my app, and that is assyncronous a few times a day.
I have also some online comunications in both directions.
The question is should I use any integration framework, or do it from scratch.
I am used to do integrations by hand, but now i have about 60-70 diferent calls from 5 diferent LOB apps to integrate.
If i should use any framework what do you would recomend?
Regards,
Rui Silva
|
|
|
|
|
In each WCF set up an extra end point that delivers JSon this can all be done in the config. Plenty of responses on google[^] covering this subject.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Tanks RAH
That will be the best approch. However i dont control the WCF services.
Even if i control them i cant be sure if they deliver basic types that can be read by non microsoft systems. (Ex. Java does not understand .net datatable)
|
|
|
|
|
I doubt there is a "framework" to service such an obscure requirement. You are going to have to build a service (wcf or webapi) that sits between the existing wcf and the client app(s) that changes takes the datatable from source and serves up the formats you want.
Bloody ugly solution to fix a really silly situation. You really should be talking to the WCF owners!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
An ESB would do that, but that's probably a big hammer to address a fairly narrow issue.
"There are three kinds of lies: lies, damned lies and statistics."
- Benjamin Disraeli
|
|
|
|
|
Sounds like you have too much "plumbing code" imbedded in your "LOB" code.
I create a separate API / Wrapper DLL for every unique web service I deal with: The "LOBS" all deal with clean interfaces; the API libs each in turn deal with whatever protocol is needed.
|
|
|
|
|
We are in the process of Architecting a mobile app to be developed using Qt and Azure Mobile Services. Coming from C#.Net background and worked on WPF applications, it's something new for us. We have used MVVM, MVP in past along with Layered architecture.
Which architecture pattern will be the best fit for this kind of application?
Read about Layered and Onion architectures. Onion architecture provides loose coupling in a better way and adhere to DDD somewhat.
Please suggest if something else will be a good fit.
Also, like to know which development methodology(DDD/BDD/TDD) shall be used considering the duration to be 4 months.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
