Mysql – Store fields that are subset of other fields on same table

design-patternMySQL

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!

Best Answer

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).

Related Solutions

MySQL – Pair of 2-Tuple Composite FKs Pointing to Same Table (Closure Table Pattern)

This is a known limitation of InnoDB. It was reported as a bug way back in 2005:
Bug #11305 On Update Cascade not working with two references to the same field

The bug was acknowledged but not fixed. The following was added to the manual section http://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html:

Between two tables, do not define several ON UPDATE CASCADE clauses that act on the same column in the parent table or in the child table.

Based on a workaround suggested in the bug, I got the update to work:

mysql> SET SESSION foreign_key_checks=0;

mysql> UPDATE father  SET `col1` = 'UPDATE_MY_ID_PLEASE'  WHERE `col1` = 'A' AND `col2` = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE closure SET `a-col1`='UPDATE_MY_ID_PLEASE' WHERE `a-col1` = 'A' AND `a-col2` = 1;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> UPDATE closure SET `d-col1`='UPDATE_MY_ID_PLEASE' WHERE `d-col1` = 'A' AND `d-col2` = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM father ; SELECT * FROM closure;
+---------------------+------+------+
| col1                | col2 | col3 |
+---------------------+------+------+
| A                   |    2 | A2   |
| B                   |    1 | B1   |
| B                   |    2 | B2   |
| UPDATE_MY_ID_PLEASE |    1 | A1   |
+---------------------+------+------+
4 rows in set (0.00 sec)

+---------------------+--------+---------------------+--------+--------+
| a-col1              | a-col2 | d-col1              | d-col2 | length |
+---------------------+--------+---------------------+--------+--------+
| A                   |      2 | A                   |      2 |      0 |
| A                   |      2 | B                   |      2 |      1 |
| UPDATE_MY_ID_PLEASE |      1 | B                   |      1 |      1 |
| UPDATE_MY_ID_PLEASE |      1 | UPDATE_MY_ID_PLEASE |      1 |      0 |
+---------------------+--------+---------------------+--------+--------+

Database Design for Payments Table in MySQL

A better design would be to have a single table to hold all the parties to a payment. If necessary, sub-type this table so companies, proprietors and customers have their own, unique sets of columns (or a separate, related table).

The payments table will then have two foreign keys to Parties, let's call them PaymentFromPartyID and PaymentToPartyID, along with amount, date etc. This way all permutations of money transfer can be tracked.

To find all the monies a person has received use

select
    sum(p.Amount)
from Payments as p
inner join Parties as y
    on y.PartyID = p.PaymentToPartyID
where y.Name = 'The person/company/proprietor of interest';

Additional Detail

A party is anyone or anything who has in interest in a payment. This would be a proprietor, customer or company, distinguished by a type:

Parties(
    PartyID
    PartyName
    PartyType     -- customer or company or proprietor
    <customer-fields>
    <proprietor-fields>
    <company-fields>)

A payment relates a payer (of any type) to a payee (of any type):

Payments(
    PaymentFromPartyID  -- FK to Parties.PartyID
    PaymentToPartyID    -- FK to Parties.PartyID
    Amount
    Date
    etc.)

The IDs used in From and To says in which direction the money flows. There is no need to have any information about a party in the Payments table, such as a type, other than the two PartyIDs.

To get all the payments made to customers (for example) add Parties.PartyType = 'Customer' to the query given above.

The Payments_Track table you list has all the attributes I list here (bar a few surrogate IDs) but is normalised.

Best Answer

Related Solutions

MySQL – Pair of 2-Tuple Composite FKs Pointing to Same Table (Closure Table Pattern)

Database Design for Payments Table in MySQL

Related Question