Better DB design to have a SUM column, or to constantly add up all relationship items per query

database-design

I have a database with an invoices table and invoice_items table. For simplicity, let's just say theinvoices table has the following structure:

| id | name | created_at |

The invoice_items has this structure:

| id | invoice_id | tital | created_at |

Pretty simple, a parent invoice table and the line items go into invoice_items with a relationship setup.

What I'm wondering is, how effective it is to has a sum column on the invoices table? Would it be bad to have all invoice_items per invoice added up on the fly to show the invoice total amount? Or would it be better to have a SUM column which is static, and is pulled automatically when invoices are listed – then updated specifically whenever an invoice changes?

Just trying to weigh up the pro's and con's – because whenever an invoice changes, the SUM column needs to be updated whereas if it was just always calculated based on the line items dynamically, would that be a drastic design flaw or be acceptable?

Best Answer

As ever - it depends.

If you have a blisteringly quick DBMS or only a handful of items in each invoice, then totalling them up on the fly would be the cleanest (and, therefore, "best") option.

The "Ivory-Tower Database Purists" would say that you should never store anything that you can derive dynamically (citing Date-of-Birth vs Age). Realistically, it depends on just how long it takes to do that derivation!

If you have an "ordinary" database, or hundreds or even thousands of items in each invoice and you need that summarised total frequently, then maintaining the summary field at the Invoice level might be the better solution. [Per-row] Triggers are the way to achieve this, so that no update to any item gets missed.

All that said, if I were in your position, I'd go with on-the-fly totalling ... at least until the application's performance began to suffer, and then I'd think about "optimising" this aspect of it.

Related Solutions

Mysql – Generating Invoices and Tracking

Cash matching

This is a cash matching problem. You can track this at one of two levels:

Compare invoiced to cash figures (somewhat sloppy but this is actually how it's done for inwards business by most Lloyd's Syndicates, often called a 'written vs. signed' report).
Maintain explicit cash allocations from cash payments broken down by invoice.

From your question I think you want to do the latter.

Typically this is done by having a separate set of cash transactions, and a bridging table that has the allocation of cash payments to invoices. If the values are equal or the cash payment comes with a single invoice reference you can do the allocation automatically. If there's a M:M relationship between invoices and payments you will need to do a manual matching process (doing this automatically is actually a variant of the knapsack problem).

A basic cash matching system

Imagine that you have an invoice table, a cash payments table and an allocation table. When you issue an invoice then you set up an invoice record in the invoices table and a 'receivable' or 'payable' record in the allocations table.

Invoice #1, $100
Allocation: a record with a reference to invoice #1, 'receivable' transaction type and $100 owing. No reference to a cash payment on this record.

Now, you get a cash payment of $100

Cash payments (chq #12345): $100
Allocation: a record with a reference to invoice #1 and chq #12345, 'cash' transaction type and -100 owing ($100 paid).

You can generalise this to a M:M relationship where you get multiple payments against a single invoice or a payment covering multiple invoices. This structure also makes it quite easy to build credit control reports. The report just needs to find invoices older than (say) 180 days that still have outstanding balances.

Here's an example of the schema plus a couple of scenarios and an aged debt query. Unfortunately I don't have a running mysql instance to hand, so this one is for SQL Server.

-- ==============================================================
-- === CashMatch.sql ============================================
-- ==============================================================
--


-- === Invoices =================================================
--
create table Invoice (
       InvoiceID        int identity (1,1) not null
      ,InvoiceRef       varchar (20)
      ,Amount           money
      ,InvoiceDate      datetime
)
go

alter table Invoice
  add constraint PK_Invoice 
      primary key nonclustered (InvoiceID)
go


-- === Cash Payments ============================================
--
create table CashPayment (
       CashPaymentID    int identity (1,1) not null
      ,CashPaymentRef   varchar (20)
      ,Amount           money
      ,PaidDate         datetime
)
go

alter table CashPayment
  add constraint PK_CashPayment
      primary key nonclustered (CashPaymentID)
go




-- === Allocations ==============================================
--
create table Allocation (
       AllocationID       int identity (1,1) not null
      ,CashPaymentID      int  -- Note that some records are not
      ,InvoiceID          int  -- on one side.
      ,AllocatedAmount    money
      ,AllocationType     varchar (20)
      ,TransactionDate    datetime
)
go

alter table Allocation
  add constraint PK_Allocation
      primary key nonclustered (AllocationID)
go


-- ==============================================================
-- === Scenarios ================================================
-- ==============================================================
--
declare @Invoice1ID int
       ,@Invoice2ID int
       ,@PaymentID int


-- === Raise a new invoice ======================================
--
insert Invoice (InvoiceRef, Amount, InvoiceDate)
values ('001', 100, '2012-01-01')

set @Invoice1ID = @@identity

insert Allocation (
       InvoiceID
      ,AllocatedAmount
      ,TransactionDate
      ,AllocationType
) values (@Invoice1ID, 100, '2012-01-01', 'receivable')


-- === Receive a payment ========================================
--
insert CashPayment (CashPaymentRef, Amount, PaidDate)
values ('12345', 100, getdate())

set @PaymentID = @@identity

insert Allocation (
       InvoiceID
      ,CashPaymentID
      ,AllocatedAmount
      ,TransactionDate
      ,AllocationType
) values (@Invoice1ID, @PaymentID, -100, getdate(), 'paid')



-- === Raise two invoices =======================================
--
insert Invoice (InvoiceRef, Amount, InvoiceDate)
values ('002', 75, '2012-01-01')

set @Invoice1ID = @@identity

insert Allocation (
       InvoiceID
      ,AllocatedAmount
      ,TransactionDate
      ,AllocationType
) values (@Invoice1ID, 75, '2012-01-01', 'receivable')


insert Invoice (InvoiceRef, Amount, InvoiceDate)
values ('003', 75, '2012-01-01')

set @Invoice2ID = @@identity

insert Allocation (
       InvoiceID
      ,AllocatedAmount
      ,TransactionDate
      ,AllocationType
) values (@Invoice2ID, 75, '2012-01-01', 'receivable')


-- === Receive a payment ========================================
-- The payment covers one invoice in full and part of the other.
--
insert CashPayment (CashPaymentRef, Amount, PaidDate)
values ('23456', 120, getdate()) 

set @PaymentID = @@identity

insert Allocation (
       InvoiceID
      ,CashPaymentID
      ,AllocatedAmount
      ,TransactionDate
      ,AllocationType
) values (@Invoice1ID, @PaymentID, -75, getdate(), 'paid')

insert Allocation (
       InvoiceID
      ,CashPaymentID
      ,AllocatedAmount
      ,TransactionDate
      ,AllocationType
) values (@Invoice2ID, @PaymentID, -45, getdate(), 'paid')



-- === Aged debt report ========================================
--
select i.InvoiceRef
      ,sum (a.AllocatedAmount)                 as Owing
      ,datediff (dd, i.InvoiceDate, getdate()) as Age
  from Invoice i
  join Allocation a
    on a.InvoiceID = i.InvoiceID
 group by i.InvoiceRef
         ,datediff (dd, i.InvoiceDate, getdate())
having sum (a.AllocatedAmount) > 0

MySQL Auto-Increment – How to Have Two Auto-Increment Columns in One Table

What you are proposing to do can only be done with MySQL cleanly under three(3) conditions

CONDITION #1 : Use the MyISAM storage engine
CONDITION #2 : Make auto_increment column part of a compound primary key
CONDITION #3 : Each auto_increment for a given type must exist in its own row
See the auto_increment documentation for MyISAM

Here is your original table layout

CREATE TABLE `invoices` ( 
  `id` mediumint unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY, 
  `invoicenumber` mediumint unsigned NOT NULL, 
  `branch` enum('A','B') NOT NULL, 
  `date` date NOT NULL, 
  `client` varchar(100) NOT NULL 
) COMMENT='' ENGINE='InnoDB';

Based on the three conditions I just mentioned, here is the new proposed table layout:

CREATE TABLE `invoices` ( 
  `invoicenumber` mediumint unsigned NOT NULL auto_increment, 
  `branch` enum('A','B') NOT NULL, 
  `date` date NOT NULL, 
  `client` varchar(100) NOT NULL,
  PRIMARY KEY (branch,invoicenumber)
) COMMENT='' ENGINE='MyISAM';

Here is an example via sample data and SQL:

drop database if exists user1162541;
create database user1162541;
use user1162541
CREATE TABLE `invoices` ( 
  `invoicenumber` mediumint unsigned NOT NULL auto_increment, 
  `branch` enum('A','B') NOT NULL, 
  `date` date NOT NULL, 
  `client` varchar(100) NOT NULL,
  PRIMARY KEY (branch,invoicenumber)
) COMMENT='' ENGINE='MyISAM'; 
INSERT INTO invoices (branch,date,client) VALUES
('A',DATE(NOW()),'John'),
('B',DATE(NOW()),'Jack'),
('A',DATE(NOW()),'Jeff'),
('B',DATE(NOW()),'Joel'),
('A',DATE(NOW()),'Jane'),
('B',DATE(NOW()),'Joan'),
('A',DATE(NOW()),'June');
SELECT * FROM invoices ORDER BY branch,invoicenumber;

Here it is executed:

mysql> drop database if exists user1162541;
Query OK, 1 row affected (0.01 sec)

mysql> create database user1162541;
Query OK, 1 row affected (0.02 sec)

mysql> use user1162541
Database changed
mysql> CREATE TABLE `invoices` (
    ->   `invoicenumber` mediumint unsigned NOT NULL auto_increment,
    ->   `branch` enum('A','B') NOT NULL,
    ->   `date` date NOT NULL,
    ->   `client` varchar(100) NOT NULL,
    ->   PRIMARY KEY (branch,invoicenumber)
    -> ) COMMENT='' ENGINE='MyISAM';
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO invoices (branch,date,client) VALUES
    -> ('A',DATE(NOW()),'John'),
    -> ('B',DATE(NOW()),'Jack'),
    -> ('A',DATE(NOW()),'Jeff'),
    -> ('B',DATE(NOW()),'Joel'),
    -> ('A',DATE(NOW()),'Jane'),
    -> ('B',DATE(NOW()),'Joan'),
    -> ('A',DATE(NOW()),'June');
Query OK, 7 rows affected (0.02 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM invoices ORDER BY branch,invoicenumber;
+---------------+--------+------------+--------+
| invoicenumber | branch | date       | client |
+---------------+--------+------------+--------+
|             1 | A      | 2012-04-21 | John   |
|             2 | A      | 2012-04-21 | Jeff   |
|             3 | A      | 2012-04-21 | Jane   |
|             4 | A      | 2012-04-21 | June   |
|             1 | B      | 2012-04-21 | Jack   |
|             2 | B      | 2012-04-21 | Joel   |
|             3 | B      | 2012-04-21 | Joan   |
+---------------+--------+------------+--------+
7 rows in set (0.00 sec)

mysql>

Give it a Try !!!

CAVEAT : At present, only the MyISAM Storage Engine supports multiple auto_increment values grouped with other columns. This is not possible with InnoDB based on auto_increment columns being tied directly to the gen_clust_index (aka Clustered Index) !!!

Best Answer

Related Solutions

Mysql – Generating Invoices and Tracking

MySQL Auto-Increment – How to Have Two Auto-Increment Columns in One Table

Related Question