[Tutorial] Optional relationships

Tips & Hacks
1

A relationship line usually tells you how many rows can match: one, or many. It should also tell you whether a match is required. That second part is optionality, and DBML marks it with a ?.

Cardinality and optionality

  • Cardinality: when a row matches, how many can it match? One, or many.
  • Optionality: does a row have to match at all? Or is zero allowed?

Most diagrams only answer the first. “One customer has many orders” leaves out whether a customer must have at least one order. Usually they don’t. Someone who signed up yesterday and bought nothing is still a customer.

Diagram notation

Each end of the line has its own symbol:

At the line end Symbol Means
bar ──┤ exactly one
circle ──○ zero or one
crow’s foot ──< one or many
circle + crow’s foot ─○< zero or many

The circle means zero is allowed. The table at that end is optional.

DBML syntax

One rule: put ? on the side of the operator that is optional.

  • >? - the right side is optional
  • ?> - the left side is optional
  • ?>? - neither side is required
  • Works with all four operators: <, >, -, <>

Example schema

Below is a small store. Each relationship is optional in a different way. Drag the tables around. To see a table’s sample rows, hover its header and click the records icon.

Table orders {
  id int [pk]
  customer_id int [ref: ?> customers.id] // a customer may have no orders
  coupon_id int [ref: ?>? coupons.id]    // an order may have no coupon
  created_at varchar
}

Table order_items {
  order_id int [ref: > orders.id]        // every order has at least one item
  product_id int [ref: ?> products.id]   // a product may never be ordered
  quantity int
}

Ref: customers.id -? customer_profiles.customer_id // a customer may have no profile

Each ? above says something different. Open the data samples and you can see all four rules at work:

Relationship Operator What is optional In the data
customers - orders ?> a customer may have zero orders Aisha Khan (3) has never ordered
orders - coupons ?>? an order may have no coupon, and a coupon may go unused orders 102 and 103 show (null); FREESHIP (51) is unused
customers - customer_profiles -? a customer may have no profile Diego (2) has an account, no profile
products - order_items ?> a product may appear in zero orders Standing Desk (4) has never been ordered

Two things worth noticing:

  • Nulls only appear on one side. coupon_id is nullable because the coupons side is optional. customer_id is never null, even though a customer may have zero orders. Optional on the referenced side puts nulls in the foreign key column; optional on the referencing side just means some parent rows have no children.

  • Leaving ? off is also a statement. order_id int [ref: > orders.id] has no ?, so the diagram claims every order has at least one item. If that is not true, the diagram is lying to whoever reads it next.

Practical impact

  • It tells you which join to write. An optional side means LEFT JOIN. Use an inner join across an optional relationship and you drop rows: count customers by joining through orders, and everyone who never ordered vanishes.

  • It tells you which columns can be null. Downstream code has to handle the missing value.

  • It records a real decision. “Can a product exist without ever being ordered?” is a modeling choice. Written as ?, it lives in the schema instead of in someone’s head.

Further reading