discussion of underlying database + data relationships

Created on 26 July 2024, 4 months ago
Updated 6 September 2024, 2 months ago

The GAAP standard in business is necessary to insure appropriate exposure to capital. These measures are necessary from "firms" (e.g. businesses) to acquire access to additional monies for operation: investments, loans, or otherwise. Double-entry accounting is important to prescribe "for every input, there is an equal and appropriate output." In order to receive outside financial aid, GAAP principles are a indication of trustworthiness with outsiders; no "funny money" exists, and an investors are supporting a viable business.

There are many related issues to this, and the diagram will help give avenues to them all. (The related issues only have a few from the first page β†’ .) The diagram is by no means complete, but it might provide insight, or a starting point, for discussions.

Problem/Motivation

This may end up just being a different way for commerce developers to utilize the code they've already written, however there may also be a furtherance of commerce based on it.

At the risk of sounding professorial, an order is an agreement between a buyer and a seller for the provisionment of goods or services. The order creates a liability incumbent upon the seller to provision the goods or services to the buyer at the purchased price. Once the buyer has received the goods or services, there is an acknowledgement (or "Receipt") which is the record which turns the former liability into an asset and releases the seller from any further liability created by the agreement (order.)

There have been discussions amongst friends about starting businesses, and it usually involved the finances and overall health of the business. There is an old saying, "spend a dollar to make a dollar," which (probably coincidental) can be taken as a reference to double-entry accounting. There is the management of the business, which includes payments for rent, local licensing fees, inventory, labor, and more. All of these are expenses that are paid (read: payments) out from the business to vendors. (Vendors for building space, contracted vendors, vendors of labor (associates/employees) and more.) There are also the payments that are coming into the business from customers, whose receipt is the sold product and/or services.

The core idea is payments come into the business and go out from the business. At its core, both entries are still just a payment. Along with the payment made, there is a formal receipt from the buyer that they received their goods or services. (This can be witnessed in stores when the large paper copy prints out of the point of sale system.) These are the two major components. There is a third component, which is the payment gateway.

The payment gateway is the person or entity which governed the payment in Commerce. The gateway is the person or thing which processed the transaction. The gateway, while not exclusively, is usually tied to the actual processing (code) of the transaction. (There is the cashier which takes the money for the order, then the person who counts it at the end of the day in the vault (payments in aggregate), then the actual deposit into the bank (transfer from the store's hands to the bank's hands).) Those are all payment gateways, depending on the process being executed.

Each of these payments (customer -> cashier, cashier -> vault, vault -> bank, bank teller -> account) has a receipt, or verification the payment was received.

Payments of product and services are also issued and collected (not just cash and credit/bank cards).

I went into a name brand store and purchased a carton of milk and a snickers bar. (Breakfast of champions!) Here's how those interactions worked:

  1. I shopped and filled my shopping cart.
  2. I placed an order with a cashier at a checkstand.
  3. The cashier gave me the total of my shopping cart.
  4. The cashier saw me pull out cash and clicked "use the cash gateway" on the point of sale system.
  5. I handed the cashier $10.00
  6. The cashier handed me my change (net difference in the order total versus the cash I handed them.)
  7. The cashier closed the drawer, which finalized the cash transaction and prompted the printing of a receipt (saying the business took $X for Y products.)

The finalization of the cash transaction (by closing the cashier's till), ran more payment gateways. The secondary gateway(s) looked at the product on the order, and processed more payments in terms of the stock changes. The payment gateway should handle different types of payments (ones linked to product variants, or services rendered). There were payments in product from the store to me. (I took the product in exchange for the cash.)

There many more payment gateways that may run as well, like unit costs for the product itself, and the difference between what the retailer paid to the vendor (and what the retailer charges the customer.) The difference between the two accounts for storage methods of the product (is it refrigerated?) and presentation to the end-user/final customer (costs of stocking the shelves to make sure the product sells).

There should be an accounting mechanism that handles all of these transactions to help secure external financing for small businesses from outside lenders and investors.

Proposed resolution

Propose a set of data relationships and refactor existing code to use them. (95% of the code is already there.) Of course, this is easier said than done. I will submit a diagram that encompasses a lot of the relational logic for public scrutiny. ("It doesn't have this πŸ“Œ [meta] Payment methods should be ephemeral Active , or it would be better placed over here...")

From there, others can be scrutinize how to refactor existing code to use the proposed database relationships. (The code is there, it just needs minor massaging.) I have to, again, reiterate my sincerest kudos and appreciation to the developers of Drupal β†’ , commerce, profile, address β†’ , all current and previous. You have all built a tremendous piece of software.)

If the diagram is determined to be illogical or otherwise useless, then this issue may be immediately closed. If it provides ideas for current implementers of commerce to better structure their clients' data, then that is great as well! However, the diagram may also provide certain pathways forward for Commerce. There are lots of entities described in the diagram, and it might be worth a look. As a regular person without having coded a single line in commerce, I submit for everyone's comments.

Remaining tasks

  1. Devise diagram which handles the Payments and Accounting for Commerce
  2. Scrutinize the heck out of it to make sure it works for all intents and purposes.
  3. Look at architectural changes in data for upgrade paths (migrate?) and devise tests to make sure it works.

User interface changes

UI changes will be coming as there will probably be different ways to store items, like payment methods and such. The interface changes should equate to easier UIs, because not so much information has to be inserted with every record. (Many more relationships to rely on to pull information from.)

API changes

Unsure yet.

Data model changes

Take a look and then discussions may be had as to how best to move forward. This is not a bug report in the traditional sense, rather a way to open a dialog on underlying data storage and a potential API-like use. There are many issues that would be impacted in a new underlying data storage mechanism, or potential rewrites of existing modules (3.x?).

Release notes snippet

🌱 Plan
Status

Closed: works as designed

Version

3.0

Component

Other

Created by

πŸ‡ΊπŸ‡ΈUnited States howards

Live updates comments and jobs are added and updated live.
Sign in to follow issues

Comments & Activities

  • Issue created by @howards
  • πŸ‡ΊπŸ‡ΈUnited States howards

    I will work on a much more succinct diagram with much more documentation on its use and more targeted toward just payments, gateways, receipts.

  • πŸ‡ΊπŸ‡ΈUnited States howards

    There has been some time that has passed. I really do not have a lot of time between the current day job and other obligations. Here is a bit more information about the thought process with regard to gateways.

  • πŸ‡ΊπŸ‡ΈUnited States lisastreeter

    Before switching to Drupal Commerce, our company used (exclusively) a custom CMS/ERP/Accounting system that was developed in-house. It adheres to all the standards you mention (GAAP, double-entry accounting, etc.) One of its limitations is that since it is custom/unique, we can never leverage existing integrations like the ones available for Drupal Commerce. When we wanted to upgrade the eCommerce component of our custom system, we spent time reviewing a large number of options like BigCommerce, Shopify, etc. Ultimately, we chose Drupal Commerce because of its solid framework and well-written code base. The vast majority of other options could not work with the uniqueness of our business requirements. From my perspective, the flexibility and lack of constraints provided by Drupal Commerce make it a superior solution.

    We still use our in-house system for accounting but have been looking into transitioning into another option like Sage, NetSuite, or Quickbooks. Now we could implement an accounting system within Drupal Commerce. (Our in-house solution is php/mysql based, so it wouldn't be a terribly difficult transition.) But we've decided that it will be better to use a commercial solution and just build/customize an integration within Drupal. So based on my experience, I don't think that adding Accounting to the core functionality for Commerce makes sense for a company that needs robust accounting functionality.

    The Drupal Commerce module already requires a massive amount of ongoing development work to maintain, much of which is done by the (relatively small) team at Centarro. I don't think it should adopt a new architectural model, to accommodate accounting functionality. Developing a custom Drupal Commerce Accounting module is one alternative. Another is developing integrations with commercial accounting software. I don't think foundational architectural changes to the Commerce Core module should be adopted; too many existing sites with significant customizations would be negatively affected.

    So my suggestion is to pursue these ideas in a custom module, separate from Drupal Commerce Core. Developers/stakeholders with similar needs can collaborate to move these ideas forward in that shared space.

  • Status changed to Closed: works as designed 2 months ago
  • πŸ‡ΊπŸ‡ΈUnited States howards

    Yes, I should have just closed this issue as irrelevant instead of reposting to it. My sincerest apologies.

Production build 0.71.5 2024