How to think about when you are building App in Anvil - Stock[inventory]/Invoice

What I’m trying to do:
I am trying to understand/learn [workflow, design ideas, coding]

How to go about building Stock/Invoice app?

These are questions coming to my mind since I started reading forum.
Link table or Simple Object?
I come from background where primary key and foreign key was a thing.
Bulk of data manipulation server side or client side?

Now lets actually think about app
I run small computer/gaming shop first thing I deal everyday bases is accounting. Thus Stock/Invoice App.

Process [my thought] - During filter process I can easily drill down so linked table.

Client give rolls of sticker → One bill came in - > record client details, bill no and items inside that bill.

Database Design [client table] → relation [one to many] to bill [gpbillno] here I used linked table to single row->[gpbillno] store bill no and link to client name. →
[gpbillno] is linked to [items] one to many using linked tale to single row.

now keeping records 3 rolls came in for printing, size in meters[3, 2, 4] total 9
after printing 1 roll sent back to client new hsn number is given to items and invoice is printed. In hand Stock 2 rolls are left.

Database Design: - Using this table stock in and out will be tracked
[transactions] table keeps record of date and time, rolls quantity and meters are tracked as in and out here and this table is linked to client, items, gpbillno.

Give me ideas for better database design or how to simplify my app.
Any ideas and improvements will be welcomed. If I am making app complicated let me know that too.

So far what I have done is used CRUD Example and created data grid 4 times around table to repeat myself [Anki - idea time and space repetitions]

Clone link:

Typically you would organize your data into minimum 4 tables for a modern web crm.

  • Customers
  • Invoices
  • Products
  • Inventory

All with internal ID columns that can link them together, and front facing Aliases, like Customer Name or email, Product Number or UPC, etc.

Customers should contain customer information and link to nothing.

Invoices should contain things like totals, customers, item information, and the amount of units sold etc. So data from every table when a row is created.

Products should contain part numbers and UPC at a bare minimum, but may also contain Description and Vendor information, and possibly url or db links to product images.

Inventory should only include the same ID’s as the product table, and the lowest unit of measure as an integer column to be modified only when things are purchased via a purchase order for the business or sold through and invoice.

More things:
If you have millions of products you will probably want an ID linked description table instead of keeping it in the product table, and possibly an image table as well for the same reasons. (reducing redundant data)

If you want more accounting insight, you will probably also want a Vendors table that includes vendor information, and and purchase order table that links the two in a “reverse invoice” where you purchase from a vendor and add to the integer column in the inventory table.

If you have more than one business location, you can have more than one integer column, or turn it into a simple object column with key/value pairs for each location OR create an inventory table with a new unique ID row that combines a location ID from a business locations (stores, warehouse etc) table with the product ID, making that locations inventory for a product a unique row.

1 Like

Since this involves tracking items with real-world dollar value, do as much as you can in your Server code. Unlike your Client code, it’s fully hidden from the users. A determined hacker can tweak the code that’s running in their browser, but not your Server-side code.

1 Like