Keys and relationships
A join needs more than matching names. It needs keys that represent the relationship you actually want.
Primary keys
A primary key identifies a row uniquely. customers.customer_id identifies one customer; orders.order_id identifies one order. The key is a property of the table’s grain.
Foreign keys
orders.customer_id refers to the customer who placed an order. The same customer_id may appear in several orders. The relationship is one customer to many orders.
Cardinality
One-to-one, one-to-many and many-to-many relationships predict join behavior. orders to order_items is one-to-many: an order can have several lines. A bridge table commonly represents many-to-many relationships.
Validate the relationship
Check key uniqueness, missing values and unmatched references. A declared foreign key helps protect integrity when enforcement is enabled; an inferred relationship still needs testing.