Relationships between tables¶
This page explains why you would declare a relationship between two tables rather than join them in each query, and how to choose between the two mechanisms KDB-X offers: foreign keys and link columns.
Why declare a relationship at all¶
Useful queries rarely live in one table. Trade records carry a symbol; the name, round lot and listing exchange of the instrument that symbol identifies live somewhere else. Answering "average trade size by security type" means bringing the two together.
q has a rich set of joins for doing that per query, and for a one-off question a join is the right tool. But where the relationship is permanent and well defined — every trade refers to exactly one instrument, for the life of the database — joining repeatedly is wasted work. A lj has to build the mapping and expand the parent to the child's length on every execution, however many times the same question is asked.
Declaring the relationship once moves that work to write time. The child table stores, for each row, a reference to the matching row of the parent, and queries then read the parent's columns through dot notation:
q)select vwap:size wavg price, name:first sym.description by sym from trade
No join appears in that query and none is performed. The benefits compound:
- Speed. A reference is an index lookup. The foreign keys white paper measures a single-column relationship resolving in roughly half the time of the equivalent
lj, and a two-column one around 37 times faster. - Less memory. Nothing is expanded to produce the result; only the parent columns a query names are touched.
- Normalization. Reference data lives once, in the parent. Children hold references rather than copies, so there is no redundancy to keep consistent.
- Integrity, for one of the two mechanisms — see below.
Two mechanisms¶
KDB-X offers two ways to express the same idea. They read identically in a query; they differ in how the reference is stored, and that difference decides where each can be used.
| Foreign key | Link column | |
|---|---|---|
| Parent | must be a keyed table | any table |
| Mechanism | enumeration over the parent's key column | row indexes into an arbitrary column |
| Integrity | enforced — an unmatched value signals cast |
none — an unmatched value reads as null |
| On disk | cannot be persisted with a splayed table | works with splayed and partitioned tables |
A foreign key is an enumeration over the key column of a keyed table. Because it is an enumeration, q validates every value as it is written: a symbol absent from the parent is rejected rather than stored. That is real protection against orphaned data, and it is the reason to prefer a foreign key wherever one will work.
A link column holds plain row indexes into an arbitrary column of an arbitrary table. Nothing validates them, so an index that points nowhere is stored happily and reads back as null — the correctness of a link is yours to maintain.
Choosing between them¶
The deciding factor is usually not integrity but where the data lives.
Keyed tables cannot be splayed, so a foreign key cannot survive being written to disk. Since any on-disk database of consequence is splayed or partitioned, that rules foreign keys out of exactly the place most data sits.
- In memory, with a keyed parent, use a foreign key and take the referential integrity.
- On disk — or wherever the parent has no key column at all — use a link column.
A link column is also the only option for a relationship that has no key to enumerate over, including a table that links to itself to represent a parent-child hierarchy.
Next steps¶
- Create and query an in-memory relationship with foreign keys, including compound and chained keys.
- Persist a relationship on disk with linking columns, in splayed and partitioned tables and across separate databases.
- Compare with joining at query time in joins.
- Read the foreign keys white paper, which covers both mechanisms and their performance in full.