How should inventory be stored in a database?

Store every stock movement as a row in a ledger table with its quantity, type and source document. Calculate the balance from the ledger, optionally caching it in a separate balance table.

Which data type should be used for money in MySQL?

Use DECIMAL with a fixed scale, such as DECIMAL(15,2). Avoid FLOAT and DOUBLE because they cannot represent many decimal values exactly and cause rounding differences.

Should records be deleted in an ERP system?

Posted business documents should not be deleted. Reverse them with a cancelling document, and deactivate master data instead of deleting it, so history remains complete.

7 ERP Database Design Mistakes to Avoid

Seven database design mistakes in ERP and business systems, from storing stock as one number to missing audit trails, and what to do instead.

The data model of a business system outlives its code. Screens are rewritten and frameworks replaced, but the tables stay, and every report depends on them. These are seven mistakes I have made or inherited in ERP-style databases, and the design I would choose now.

1. Storing stock as a single number

A quantity column on the product table is easy to build and impossible to explain. When the number is wrong, nobody can say why. Keep a stock ledger instead: one row for every movement in or out, with its source document. The current balance is the sum of the ledger, optionally cached in a balance table that can always be rebuilt.

stock_movements
  id, product_id, warehouse_id, quantity (+/-),
  movement_type, source_type, source_id, moved_at, created_by

2. Reading today's price for yesterday's invoice

If an invoice line stores only a product_id and looks up the price when displayed, every old invoice changes when the price list does. Copy the values that were true at the time onto the line: unit price, discount, tax rate, even the product name. Documents are history, and history does not change.

3. Using floating point for money

FLOAT and DOUBLE cannot represent many decimal fractions exactly, so totals drift by small amounts. Use DECIMAL with a fixed scale, for example DECIMAL(15,2) for amounts and more decimal places for unit prices and rates. Decide the rounding rule once and apply it in one place.

4. No audit trail

Sooner or later someone asks who changed a credit limit and when. Add created_by and updated_by columns to every table, and keep a change log for sensitive ones that records the old value, the new value, the user and the time. It is far easier to add on day one than after the question is asked.

5. Deleting business records

A posted invoice must never be deleted or edited. Cancel it with a reversing document, as accountants do. For master data such as customers, use an active flag instead of a delete, so old documents still point to something.

6. Status as a free-text or magic number

A status column holding 1, 2, 7 with meanings known only to the code leads to reports nobody can trust. Use named values, document the allowed transitions and record each status change with a timestamp, so you can measure how long documents wait at each stage.

7. Skipping foreign keys and indexes

Without foreign key constraints, orphan rows appear and reports quietly lose or double-count data. Declare the relationships and let the database enforce them. Index the foreign keys and the columns reports filter on, usually dates and organizational units. See MySQL query optimization for reports for how to choose those indexes.

A rule that covers most of them

Record what happened, not just what is. Movements, documents and changes are facts; balances and statuses are summaries of those facts. A database built on facts can answer questions nobody thought of when it was designed. For how this fits into application structure, see Laravel ERP architecture.