Design database for shop with invoice

I have that database structure (I can change it):

But what about a corrective invoice ? I will be doing PDFs based on data in this database. In the corrective invoice I must have old values and new values, for example when customer order first 5 oranges and then he said that he wants only 1 orange so i must write that (old and new quantity) in the corrective invoice. But when I modify in table quantity in table POSITIONS_ORDER I won’t have old quantity. So how should I make database structure ?

What you have identified is a version management requirement. There are a few ways to fudge your way around this requirement, but ultimately you will need to capture versions of orders and related data in order to produce point-in-time invoices that represent the current values of an order versus the original values.

At a minimum you will need to include version IDs and/or date/time stamps in the keys of your order and order lines tables (the table you have named POSITIONS_ORDER), so that you can isolate the original state of an order (as version 0 of the order header and detail records), but also capture a full history of changes over time. This would give you the ability to print invoices as at a specified date, and would also allow you to provide users with an option to print invoices showing the original state of an order together with the current state. This last invoice would be what you are thinking of as a “corrective invoice”.

But your requirement (and your problem) is bigger than that. Over time, any number of factors can affect values on an invoice. For instance, what if the customer places an order, and then before the order is delivered:

  • The customer’s discount status changes
  • The product prices change on a scheduled effective date
  • The user creates a campaign, discount or special offer that the current order qualifies for
  • The customer adds or cancels products on the order
  • Shipping is unable to deliver some items or needs to substitute some items at different prices
  • On a scheduled date, a new service charge is applied to the order

In all of these cases (and many more), the invoiced items, prices and charges can change whether or not the customer changed his order. In principle, you may need to provide version tracking for any related data which may affect the values on an invoice as at any given date. In general you can lay the foundations for this by adding version IDs and/or date/time stamps to the keys of all tables that need version management (which might be any data which is not purely descriptive).

Features such as the “corrective invoice” you are considering may become significant additional work for solution developers, and you should clarify the requirements with the customer, estimate the work and obtain approval before you start building code for it.

On a smaller side note decide on whether a table name should be plural or singular, you have orders and invoice etc. I prefer singular but am not too fussed as long as there is consistency. It is the little things that can end up most annoying and playing a constant subconscious game of “is it plural?” still takes up thought which can sometimes really be needed else where when designing and typing queries. :slight_smile:

It is pretty hard mentally keeping track lots of these little boolean cases and then type them the right way round all the time. Even if you remember them the right way round the hands may differ in opinion.