Store fields that are subset of other fields on same table Just wondering what the optimal design pattern would be for the following data I'm trying to model.
I have a table that stores information for Orders that have been placed by customers.
Orders can be either Invoiced or Opened.
Currently, the table stores data about the total Order information (so...invoiced+open).
I.e. totalAmount, totalCost, etc...
The Invoiced and Open information has to be calculated in code and then Updated per row. Technically speaking, this information can just exist in memory of the app after the calculations. But..I figure...storage is cheap and it'll probably be useful to store this data in the table for later on.
However, I feel like it's kinda awkward and can lead to large rows.
I.e I'd have
totalAmount,
totalCost,
openAmount,
openCost,
invoicedAmount,
invoicedCost
Are there any better ways to do this?
Thanks!
Generally in a Sales system, Sales Orders and Invoice Orders are two different entities related to each other. That's probably a good way to normalize the columns you're referring to. In general, it's better to normalize your tables to reduce data redundancy and improve query efficiency, as opposed to very wide Tables.
Also generally Orders tables (both Sales and Invoices respectively) are generally broken out into two tables themselves: 1) OrderHeader and OrderLine (you can name these more specifically as appropriate).
The Header tables store general information such as the SalesDate (or InvoicedDate for InvoiceHeader) and customer information. It can also store the totals as well but isn't necessary. The Line tables store individual lines for each item of the Order and the information specific to that item. Each line would have the individual cost of the item (e.g. SalesCost for SalesLine, InvoicedAmount for InvoiceLine).