determine correct relations in ERD - Customer Orders use case

The below ERD is taken from a professional architecture reference, however I have a concern in the relation between Customer and Invoice , I believe that relation between Customer to Order , and Order to Invoice is enough and that adding a relation between Customer and Invoice is redundancy. More over I am not getting the point in making a relation between Customer and Invoice but not making a relation between Customer and Payment . So 2 questions here:
1- Is it correct to make relation between Customer and Invoice
2- If yes, then why not make relation between Customer and Payment

Sometimes relations are not strictly required, but are convenient to make queries easier or more performant. That could be the case for Customer-Invoice, but we can’t tell for sure; you’d have to ask the person who made that relation. Same for your second question, maybe it wasn’t necessary?

Yes, if you want to get a list of all invoices for a customer, it’s convenient to to have the customer_id in every invoice record.

2 Answers 2

I believe that relation between Customer to Order , and Order to Invoice is enough and that adding a relation between Customer and Invoice is redundancy.

Probably yes, in case the Customer->Order->Invoice chain shall always define the same set of invoices as the Customer->Invoice relationship. But one cannot tell only from the diagram, with no extra information: to be sure, one also needs to know the semantics of the relationships, and how they are supposed to be maintained.

For example: maybe the system allows some kind of invoices without any order (or at least without orders stored in this system). If the meaning of "Customer->Invoice" is to represent exactly these invoices, for which no order record exists, then the relationship is obviously not redundant, quite the opposite.

But let us assume "Customer->Invoice" is just a redundant relationship. You asked if this relationship is "correct" - but that's actually the wrong question. Redundancy in a database is not a question of correctness. Redundant relationships are usually not required from the domain modeling point of view, but they are sometimes helpful for technical reasons:

• adding redundant relationships can bring a performance benefit for certain queries
• on the other hand, they may have a negative impact on insert/update performance and introduce the risk of getting inconsistencies.

The point is: these are reasons you typically cannot foresee if they will apply, when you are just creating the ERM. So the strategy here should be not to add any redundant relationships "just in case" beforehand, but only after you implemented a system and notice real-world performance issues in queries for which you know redundant relationships could speed them up.

nice tips, if you can elaborate with more references for cases where redundancy is accepted or even recommended i'd be thankful

@osamayaccoub: design decisions for or against redundancy is a regular repeating topic which I had to deal so often in real-world programming that I actually cannot give you any specific reference. Maybe this Wikipepdia article about data redundancy is what you are looking for.

My main concern with this diagram is that, in practice, (sales-) orders and invoices are not necessarily one-to-one.

There can be multiple invoices for one order. This may occur due to the order being deliverable and/or payable in instalments, including where credit terms are offered, or where a production or supply issue causes an unexpected delay in fulfilment of an element of the order, and so a part-delivery is made and part-payment called for.

Many invoices per order may also occur due to invoices having to be modified after issuance - the recording of invoices is subject to regulations in many jurisdictions, and invoices cannot be (lawfully) adjusted or deleted after the document has been issued outside the organisation (though they can be added to with narrative, or declared cancelled and withdraw). This could easily occur if, say, a buyer is entitled to a discount based on overall ordering volumes within a timeframe, and (because that discount is a function of many orders, a timeframe, and a contractual agreement) this is not correctly applied in the first invoice, and the buyer then queries the situation.

And perhaps less necessary, but certainly possible, there can be multiple orders (by the same buyer) for one invoice. This may occur where a buyer places a series of orders in short succession, but are billed at once for administrative convenience and to avoid a flurry of paperwork.

There can also be invoices without orders, and certainly in this case the relationship between the customer and invoice could not be implied through the mutual relationship with an order. This may most obviously occur when the buyer becomes liable for a fee without having explicitly requested it - a storage fee, for example, where a buyer has failed to take control of goods at the works where they are made available for collection.

A sales order cannot simply be generated for this storage fee, because there would be no associated and matching purchase order from the buyer. At best, creating a fictionalised sales order will simply move the reconciliation problem around to the interface between sales orders raised and purchase orders received (which is something controlled by the buying organisation), instead of the interface between sales orders raised and invoices issued.

It is rarely the case in real-world accountancy that one business document is always created mechanically from another. There has to be latitude for human intervention.

Another issue mentioned is that of "redundancy". In fact, there rarely is redundancy. What if your buyer moves addresses, or changes company name, after the invoice is issued? The invoice, as I say, is subject to laws that require it to be preserved in the state it was issued. Reconstructing the details that applied at the time from auxiliary name and address tables will cause trouble for the programmer, and done incorrectly could cause criminal penalties.

Another issue is the retention of data. Eventually, in systems that continue to work over many years, old data must be deleted, both for efficiency reasons and nowadays for regulations relating to data protection. Pruning an invoice table (or a master/detail pair of tables) that contains all of the document within a single row (or single header row in the master table, and any subordinate rows in the detail table) is trivial. Pruning a web of tables with scattered data that is used in multiple places is less easy, and again risks criminal penalties if programmer error destroys the wrong data and corrupts the integrity of records subject to a mandatory retention period.

The lesson is this. When dealing with data that is intended to represent documents that fall within the purview of the accountancy profession, store the data in ways that correspond as closely as possible to how they are stored and represented on paper by accountancy professionals. Don't assume that one order relates to one invoice, and that order lines correspond to invoice lines, unless a company accountant tells you that in their company it is, and that it always will be.