Invoicing Database Design

Three tables to start. Customer holds an identifier and a name. Invoice records which customer placed it, where, and when. InvoiceLine adds one row per item on each invoice.

CREATE TABLE Customer (
    Id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    Name VARCHAR(255) NOT NULL,
    PRIMARY KEY (Id)
);

CREATE TABLE Invoice (
    Id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    CustomerId INT UNSIGNED NOT NULL,
    City VARCHAR(255) NOT NULL,
    Date DATETIME NOT NULL,
    PRIMARY KEY (Id),
    FOREIGN KEY (CustomerId) REFERENCES Customer(Id)
);

CREATE TABLE InvoiceLine (
    Id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    InvoiceId INT UNSIGNED NOT NULL,
    Item INT UNSIGNED NOT NULL,
    Quantity INT UNSIGNED NOT NULL,
    Price DECIMAL(5, 2) UNSIGNED NOT NULL,
    PRIMARY KEY (Id),
    FOREIGN KEY (InvoiceId) REFERENCES Invoice(Id)
);

Two customers, three invoices and six line items, enough to exercise the schema.

INSERT INTO Customer (Name)
VALUES ('George Holt'), ('Al Housten');

INSERT INTO Invoice (CustomerId, City, Date)
VALUES (1, 'Lancaster', '2021-11-04'),
       (2, 'Riverside', '2021-11-12'),
       (1, 'Victorville', '2021-11-17');

INSERT INTO InvoiceLine (InvoiceId, Quantity, Item, Price)
VALUES (1, 4, 4, 44.95),
       (1, 2, 3, 29.95),
       (2, 1, 4, 44.95),
       (2, 7, 1, 19.95),
       (2, 3, 2, 39.95),
       (3, 2, 3, 29.95);

The schema has two weaknesses. First, there is no current customer address, since the city can only be inferred from a customer's last invoice and that may be stale for infrequent buyers. Second, there is no product catalogue, so item prices live on InvoiceLine and the current price has to be inferred from the last sale, which is expensive and wrong for rarely-sold items.

Rename the loose Item column to a proper foreign key ProductId. A new Product table now holds the description, price and weight for each item.

ALTER TABLE InvoiceLine CHANGE Item ProductId INT UNSIGNED NOT NULL;

CREATE TABLE Product (
    Id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    Description VARCHAR(255) NOT NULL,
    Price DECIMAL(5, 2) NOT NULL,
    Weight INT UNSIGNED,
    PRIMARY KEY (Id)
);

INSERT INTO Product (Description, Price, Weight)
VALUES ('Phone Charger', 19.95, 40),
       ('Protective Phone Case', 39.95, 35),
       ('USB Charging Cable', 29.95, 5),
       ('HDMI Cable', 44.95, 10);

ALTER TABLE InvoiceLine ADD FOREIGN KEY (ProductId) REFERENCES Product(Id);

Add a City column to Customer, backfill it from the most recent invoice and then make it NOT NULL. A new Delivery table tracks shipment status, and each Invoice now links to its delivery.

ALTER TABLE Customer ADD City VARCHAR(255);

UPDATE Customer SET City = 'Victorville' WHERE Id = 1;
UPDATE Customer SET City = 'Riverside'  WHERE Id = 2;

ALTER TABLE Customer MODIFY City VARCHAR(255) NOT NULL;

CREATE TABLE Delivery (
    Id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    City VARCHAR(255) NOT NULL,
    Status ENUM('Registered', 'In Transit', 'Complete') NOT NULL,
    Date DATETIME NOT NULL,
    PRIMARY KEY (Id)
);

INSERT INTO Delivery (SELECT Id, City, 'Complete' AS Status, Date FROM Invoice);

ALTER TABLE Invoice
    ADD DeliveryId INT UNSIGNED UNIQUE,
    ADD FOREIGN KEY (DeliveryId) REFERENCES Delivery(Id);

UPDATE Invoice SET DeliveryId = Id;

Mark a customer as Premium once they have more than two invoices. An AFTER INSERT trigger on Invoice recounts and updates the flag on every new insert.

ALTER TABLE Customer ADD Premium BOOLEAN NOT NULL DEFAULT 0;

CREATE TRIGGER CheckPremium AFTER INSERT ON Invoice
FOR EACH ROW
UPDATE Customer
SET Premium = (
    SELECT IF(COUNT(Id) > 2, 1, 0)
    FROM Invoice
    WHERE CustomerId = NEW.CustomerId
)
WHERE Id = NEW.CustomerId;

Insert a third invoice for customer 1, and the trigger promotes them to Premium.

INSERT INTO Invoice (CustomerId, City, Date)
VALUES (1, 'Victorville', '2021-11-17');