2

I am designing an online accounting system as a college project. I have got the following simple transactions.

  1. Sales on cash $300

  2. Cash burrowed from Smith $250

  3. Electricity bill paid $50

enter image description here

I have referred Double Entry Accounting in a Relational Database and designed the above Transaction table(minus sign means the amount is credited). The id 1 and 2 belong to transaction 1, id 3 and 4 belong to transaction 2 and so on. How can I identify each two rows as a single transaction in MySQL database.For example if the first transaction to be selected how can I do it since there is no relationship between the two rows. If this design does not sound good can you suggest an alternative.

Brian Tompsett - 汤莱恩
  • 5,195
  • 62
  • 50
  • 120
Rav
  • 1,023
  • 4
  • 16
  • 18
  • you can add transaction_id filed for you table. – Sara Mar 26 '12 at 12:59
  • Thanx for the response.I guess then it has to be manually added for each transaction and there is no way I can auto-increment the field. I can't find a way to connect the accounts in different rows. – Rav Mar 26 '12 at 13:04
  • Technically - from a strictly normalised viewpoint, what you've got there is 2 entries per transaction. You could hold the transaction ids in a separate table and auto-increment them there, then just reference them in this "transaction" table. Of course, if you're going to hold information on trillions upon trillions of transactions an auto-incremented integer might not be as useful as a unique string determined by the application anyway... – CD001 Mar 26 '12 at 13:15

3 Answers3

0

Good Job.

The only thing I might add is two number columns for debit and credit. That would look just like a journal in the text books or old bookkeeping books. It would be a little more visibly obvious and handle any number of double entries. Your current amount column could be calculated by debit - credit if you like.

I do not think you need to make it relational unless you wanted a specific chart of accounts as a table, more complicated and less elegant. But, for more flexibly you could generate real time chart of accounts from the journal.

Luca
  • 1
0

Since you are using MySQL relational database, you can add several tables.

To answer your question, you need to create another table to hold general information for each transaction (e.g. you could call it a TRANSACTIONS). This table could include Transaction ID, and Date Column. Now, reference the Transaction ID column of the TRANSACTIONS table in your currently existing table. This means record 1 and 2 will have one Transaction ID and so you can reference it as such in your queries.

Qback
  • 2,605
  • 2
  • 15
  • 29
Philip
  • 1
0

You should drop the two line design unless it's really necessary. I would merge the rows and add an extra field where you could store if it was cash or not and the amount value would be + when credited and - if debited (or reversed, I"m not good at accounting :))

slash197
  • 8,782
  • 6
  • 38
  • 68
  • I tried that way too however when there are more than two accounts we cannot merge, can we? – Rav Mar 26 '12 at 13:10
  • Well, give an example. You mean more than two rows for the same transaction? If so, what's the difference? – slash197 Mar 26 '12 at 13:14
  • I mean an entry like this Writing off bad debts. DR-Allowance for doubtful debts DR-GST CR-Recievables Three accounts will take three rows in the above table. I am new to designing can you show me how to merge the rows and add the extra field like you said. :) – Rav Mar 26 '12 at 13:30
  • If you have more entries for a transaction you should go with what CD001 suggested in his comment – slash197 Mar 26 '12 at 13:41