CODEWITHSUNDEEP
databasedatabase-designdatabase-schemaaccounting
Raf
Raf

Reputation: 708

Accounting Payables/ Receivables database schema

I searched for this topic and there are lots of explanation to collect. Mostly to do separate tables for a/p and a/r.

I'm thinking of another approach from where to combine the two tables into 1 because a/c and a/r are 98% similar and only differ by 1 or 2 attributes (e.g. a/p: vendor/supplier).

Question:

Is it possible to merge the 2 tables and just categorize each transaction with Transaction_Type with values of Acnt_Payable or acnt_Receivable?

I know this is possible to do but is this a good practice? and what are the circumstances that my system will face when dealing with Reports?

Upvotes: 2

Views: 1923

Answers (1)

Krista K
Krista K

Reputation: 21851

Your question is: "another table or another column". Given that I've done lots of MySql and accounting database manipulation, I wouldn't hesitate to have type as an extra column.

The deal with reports is that you'll need to rework the code that pulls the reports. Having the extra column means less joins (if you have them now). You will need to step up your error checking, as doing a change to already designed software seems to always catch me off guard where I hadn't before considered a nuance of the design that is suddenly a paradigm shift for the logic.

I'd say rewrite the report code from a blank canvas; between your increased experience from "last time" and a fresh look at it this time, you might do a much better job of it.

IMHO, for performance aspects, most businesses and most average coders are OK with an off-the-shelf install of MySql or PostgreSQL. When I observe a query taking more than a second, it's usually because I need to add an index. Given that, you could have yet another table with the IDs representing AR vs AP (or any other human-terms), as I think numbers will make faster performance.

Once a company gets to where performance really is an issue, they'll have the resources to hire a real MySql guy to come in and mess with it. Sarcasm: Unless, of course, you're using quickbooks, in which case, once you're past a 20 transaction-per-day mom and pop joint, you're overtaxing the software.

Upvotes: 1

Related Questions