Search code examples
phpmysqldatabase-designaccounting

MySQL table designing for an Accounting system

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.

Solution

  • 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 :))