When should you use interleaved tables versus foreign keys?

Both express a parent-child relationship, but they optimize for different things. Interleaved tables physically co-locate child rows with their parent, so use them when the child is almost always accessed together with its parent, such as an order's line items, and when the child's primary key can meaningfully start with the parent's key.

Foreign keys enforce referential integrity without changing physical layout, so they suit relationships where the child is queried independently, has its own natural primary key, or where interleaving would force an awkward composite key. Foreign keys also support cross-hierarchy references that interleaving can't express, since an interleaved table can only nest under one parent chain.

A practical rule: reach for interleaving when co-location speeds up your main access pattern; reach for a foreign key when you mainly need integrity checking and independent access.

Invest now in Acorns!!! 🚀
Join Acorns and get your $5 bonus!

Acorns is a micro-investing app that automatically invests your "spare change" from daily purchases into diversified, expert-built portfolios of ETFs. It is designed for beginners, allowing you to start investing with as little as $5. The service automates saving and investing. Disclosure: I may receive a referral bonus.

Invest now!!! Get Free equity stock (US, UK only)!

Use Robinhood app to invest in stocks. It is safe and secure. Use the Referral link to claim your free stock when you sign up!.

The Robinhood app makes it easy to trade stocks, crypto and more.

Webull! Receive free stock by signing up using the link: Webull signup.