Database / Google Spanner Database Interview questions
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.
More Related questions...
