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 |
Leaving ? off is a claim too. order_id int [ref: > orders.id] says every order has at least one item.
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.