Conceptual, logical, physical: trace a guest-checkout requirement

Guest checkout changes the meaning of customer

The initial rule says every order is placed by a registered account. A new requirement permits guest checkout. Simply making customer_id nullable changes the DDL, but it does not answer how the guest is contacted or what survives when an account is removed.

The conceptual model settles the vocabulary. The logical model identifies the facts and their cardinalities. The physical model chooses actual types, constraints, and indexes for the selected database. Keep a trace from each physical field to the rule that required it.

The optional relationship changes the query

CREATE TABLE customers (customer_id BIGINT PRIMARY KEY);
CREATE TABLE orders (
  order_id BIGINT PRIMARY KEY,
  customer_id BIGINT,
  buyer_name VARCHAR(80) NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
SELECT o.order_id, o.buyer_name, c.customer_id
FROM orders o
LEFT JOIN customers c ON c.customer_id = o.customer_id;

Create one member order and one guest order with a NULL customer id. The LEFT JOIN should return both. Replacing it with an INNER JOIN removes the guest order; that is a query bug if the screen must list all orders. An unknown non-NULL customer id must still be rejected by the foreign key.

Account deletion is another requirement

The buyer_name value here describes the name at checkout, not the current account name. Changing a profile should not rewrite an old receipt. Decide separately whether account removal sets a reference to NULL, retains an inactive account, or is restricted while transactions must be kept.

Retention and deletion rules determine which contact facts belong on an order and how they are protected. Avoid copying every profile attribute “just in case.” In YourERD, logical names can express buyer and account separately while physical names and NN flags record the implementation. Review a member order, guest order, and removed account before considering the schema complete.