Hi. Started creating an invoice/quote/customer database/ tax data GUI for my own business. The GUIs are ongoing fine, all done in Python which I am ok at. But the database behind it I am having trouble planning. Before now, just had a single database with a single table, so no real problems. But this will have a quote page, an invoice page, a client lookup and so on and so I am a bit befuddled as to best form for tables.

For example. I create a quote and all data goes into a table ( or tables?) If I do an invoice for this quote I can just pull up all that info into a quote. However, some invoices won't have a quote. So I create a new invoice but now I also have a client in this form.

So, do I have 1 table for client data, 1 table for quote details without client data, 1 for invoice, no client data etc etc.

Is there an accepted norm for this or somewhere I can find an example?

Thank you

For me, quote, invoice, purchase order, sales order and manufacturing order are all the 'same', only the head is a bit different. So I have a table order_head and order_lines and order_type. This plus the customer- or supplier data are an invoice or order etc. Type can be changed from quote to order or invoice or it can be created as an order directly

That's essentially the beginnings of an ERP system.

If you have a dire need for "simplicity" then it's probably worth making it yourself, but I've given up long ago trying to reinvent the wheel and just use ERP systems that are already great at all those things.

If you have a proper web-server, you may even get it for free by using a great open-source one. See ERPNext.

That said, if you wish to re-invent - the Table layout will be up to the complexity you choose.

Certainly there should be a table each for the following:

Company Data Tables:

• Company Name, Registration, Print Headers, Trading Currency etc.
• Warehouses
• Items (Goods/Services you Buy, Manufacture, Supply and/or Sell)
• Inventory (Stock item counts per Warehouse)
• Journals (Stock transfers, movements, adjustments, etc.)
• Price Lists (Multiple Named lists of Price per Item)

Supply Chain Data Tables:

• Suppliers,
• Purchase Orders (Links to Suppliers, Stock Items)
• Purchase receipts (Links to Purchase Orders, Warehouses, Journals)

Sales Data Tables

• Customers
• Quotations (Links to Customers, Stock Items and Price Lists)
• Sales Orders (Links to Customers and Quotations)
• Delivery Note (Specifies Item Qty's on Sales Order that were delivered)
• Invoice (Makes the Delivered Qty's from Delivery Note into payable Invoice)
• Payments (Link to Invoices - Record if and when Payments were made)
• Credit Notes (These are really just negative Invoices)

You can of course undercut many of these with simplistic record keeping, but take it from someone who has done this dance a few times - sooner or later, they will want all of these things (unless the company doesn't grow at all, which is an equally bad outcome).

I have not touched on the Financial/Bookkeeping side of things because usually a small company that might be interested in making a simple buy/sell system would use some off-the-shelf bookkeeping product - but a proper ERP system has everything included.

My advice: Use ERPNext - Like SQlite, It's free (open source[1]), it's complete, it's robust, actively maintained with a large community and it will save you years of work.

Cheers! Ryan

[1]: Runs only on Linux Servers, its source code and engines are MySQL + Python + NGinx, which may or may not be platforms you like. They do offer a paid option if you'd like them to host it for you, but it's easy to do if you are Linux conversant.

You need to be thinking about what individual facts belong together and which do not. A careful study of "What is Normalization?" would help a lot.

Here is how such concepts would apply in some of your cases.

An invoice contains fact relating just to single invoices, such as the amount said owing, who owes it, and the invoice date. But there there could be more than one quote for any given transaction or no quote. This suggests that quotes will be in their own table. Likewise, any given client could be given multiple invoices and quotes. So clients must have a table separate from both quotes and invoices. Although you left it unstated, an order (without a quote) or contract resulting from an accepted quote (or accepted counter-offer) is independent of quotes and invoices. You may have to send multiple invoices against a fulfilled order.

I'm sure if you search diligently, you can find some example schema for such things. However, I think you would do yourself a favor by contemplating why different tables are used to represent different sets of related facts about (a subset of) the world. That link on normalization will promote that contemplation.

Hi

I think Ryan Smith (msg#3) and others pretty much answered this. It is an exercise in re-inventing the wheel. However, if you do want to go through the exercise, I came across this article sometime ago:

https://towardsdatascience.com/how-to-build-an-accounting-system-using-sqlite-2ce31f8b8652

that might be of interest. The article is mostly a training exercise in database design and implementation by an accountant using SQLite.

Another possibility, in addition to Ryans suggested ERPNext product, there are a number of Open Source ERP products available if you search for them. One example is LedgerSMB. I personally have not used this product and am not in any way involved with it. I only offer it as a possible option.

John

Ok, yes. That simplifies things in my head.

Hi. Sort of. What you have described is waaaaaaay more than I would ever need. There are off the shelf programs I could use but its also a learning exercise for me. I have a system that works but very manual, trying to eliminate the cut and paste element of it with database. But thank you.

Thanks. Will look at it - could be what I need.

Ok. Will read.Thanks

Hi How have you got on? I (we) would be interested to see what progress you have made and possibly come up with suggestions for you.

Cheers cheers

Joey8

What I cobbled together many moons ago for some simple stuff. Everything, item, contact, that can apper in the balance sheets https://gist.github.com/NYKevin/9433376 has an account(account_id). Then there is a lot of (trigger)machinery and a bit more in the tables for history of changes.

Comments welcome.

CREATE TABLE IF NOT EXISTS order_head(
 order_number INTEGER NOT NULL UNIQUE PRIMARY KEY,
     relation INTEGER NOT NULL REFERENCES account(account_id),
   order_type INTEGER NOT NULL REFERENCES order_type(id),
      ts_from TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
       ts_eol TEXT DEFAULT NULL
);


CREATE TABLE order_body(
 order_number INTEGER REFERENCES order_head(order_id),
   order_line INTEGER NOT NULL,
         item INTEGER NOT NULL REFERENCES account(account_id),
     quantity REAL NOT NULL,
        value REAL NOT NULL,
quantity_recv REAL,
   value_recv REAL,
      ts_recv TEXT,
        state INTEGER NOT NULL REFERENCES order_state(id) DEFAULT 1,
      ts_from TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
       ts_eol TEXT DEFAULT NULL
);
CREATE UNIQUE INDEX IF NOT EXISTS order_body_number_line
ON order_body(order_number, order_line)
;

CREATE TABLE order_type(
           id INTEGER NOT NULL PRIMARY KEY,
         type TEXT NOT NULL UNIQUE,
        short TEXT NOT NULL UNIQUE,
           ts TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
       ts_eol TEXT DEFAULT NULL
);

CREATE TABLE order_state(
           id INTEGER NOT NULL PRIMARY KEY,
        state TEXT,
      ts_from TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
       ts_eol TEXT DEFAULT NULL
);

Hi INGO OK... For my two cents worth (Yes, there are lots of ways of doing this, so everyone please don't flame me!)

You would need a couple of master files and a couple of transitory files (when I say files I mean tables)

• Customer master file (for names/addresses/details/balances etc)
• Item master file (for all your saleable products/services etc)
• Orders file(s) (headers/details) (for quotes and/or orders, a quote is a different type of order), this would then pass data over into
• Invoice file(s) (headers/details) for all billable products/services)

Here is a quick diagram of the files

You would need a handful of programs

• Customer edit prog
• Item edit prog
• Order entry/edit prog
• Order Confirmation prog (turns order/quote into an invoice)
• Sales Invoice payment prog (when customer pays you)
• Order/Quote print
• Invoice print
• Customer Statement print
• VAT100 print

---

The above could be the basic system which you could then add onto if you require, like:-

• General Ledger progs (like sales/purchases/vat/expenses accounts etc)
• Journals program (for altering accounts and balances)
• Purchase Invoice entry/edit
• Aged Debtors Print (How much debt you are owed from your customers)
• Aged Creditors Print (How much credit you owe to your suppliers)
• Company Balance sheet etc etc

If you want to chat further please get intouch, I would be glad to help, Cheers!, Joe :¬)

joe_evans{at}eml{dot}cc

A repeated note to everybody - there are lots of ways to do this - so please do not flame me

Thanks Joe!

Just to give a bit more background, it's part of a bigger thing. I used order_header, order_body, due to lack of a better name. form_header, form_body may be better, but is also very general. As the structure and the data for sales- or purchase order, invoice etc is all the same I didn't differentiate to different tables. There is a complete contact database. Contacts can have an account_number, or not. With an account_number they can have several roles like customer, supplier, employee, tax-collector etc. The type of "order" header they are in defines their role. It's a one man shop here, but I could create a monthly order to pay an employee. There is a complete database for 'items'. These can be bought ones, produced intermediates and final ones. The are partley produced on recipe and partly on bom. They all also have an account_number. All transaction, also internal stock moves are managed through the balance sheets, so materials, labour, process steps and money are always directly linked. As soon as the state of anything changes a copy of the 'old version' is made and references to the new version. So for everything a time-line can be constructed. The form_header_id stays the same when an offer changes to an order and from there to an invoice.

Ingo

mmmm, I see this looks like it was an old spread-sheet system with several pages, yes? I think (Do you?) now is a good time to re-do it into some sensible tables? Or are you snowed under with work atm? A one-man shop? Oh heck :¬) BOMP - wow I havent worked on BOMP systems for a while

I think you are correct about creating a monthly order for a helper