MySQL

An order import from a marketplace may arrive twice, for example after a timeout between two systems. The unique pair of marketplace and external_order_id therefore belongs in more than a PHP condition: the database enforces it even during parallel processing. A foreign key to the customer also prevents storing an order with a nonexistent identity.

An application transaction creates the order, its line items, and an audit record. If any step fails, ROLLBACK reverts the local changes as a unit. An index on status and time supports a specific administration listing; it is not added preemptively to every column.

SQL (MySQL)

CREATE TABLE customer (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(255) NOT NULL,
  UNIQUE KEY customer_email_uq (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE shop_order (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  customer_id BIGINT UNSIGNED NOT NULL,
  marketplace VARCHAR(32) NOT NULL,
  external_order_id VARCHAR(64) NOT NULL,
  status VARCHAR(32) NOT NULL,
  created_at DATETIME NOT NULL,
  CONSTRAINT shop_order_customer_fk
    FOREIGN KEY (customer_id) REFERENCES customer (id),
  UNIQUE KEY shop_order_external_uq
    (marketplace, external_order_id),
  KEY shop_order_status_created_idx (status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;