22.3 Joins and Table Relations¶
In Acinta you don't define joins table by table. Tables are joined through the dimensional units their fields are mapped to: when the invoice lines' customer field and the customer table's number are both mapped to the Customer unit, Acinta knows how to join them. This keeps the model simple and consistent.
Joins Through Units¶
Map key fields to units – see 23.2 Linking Units to Tables. The Warehouse diagram then shows the possible joins as black lines, from the unit's lookup table to every table mapped to it.
Relation view (WAREHOUSE → Display; HOME → Warehouse before build 20353) controls the lines: All relations (default; the selected table's relations in bold), Selected relations (only the selected table's) or Hide relations.
Tick Route lines around tables in the same drop-down to draw the join lines around the tables, with right angles and rounded corners, instead of straight across (new in build 20355). It's on by default (build 20365 and later; before, off) – if you had already ticked or unticked it, your choice is kept. Modeller remembers the choice for you – it isn't saved in the project. Routed lines that run along the same stretch are drawn a little apart, so each can be followed (build 20360 and later).
To follow one join line among many, point at it: it's drawn green, on top of the others. Pointing at a table draws its lines stronger and on top (build 20360 and later).
Acinta chooses the joins for each query from the units used. Enforce NULL equality in joins (data source options, on by default) makes empty keys match each other.
Table Relations¶
Sometimes two tables must always be joined on fields that aren't a unit – e.g. the second part of a compound key. Create a table relation:
- Drag a field that isn't mapped to a unit onto the matching field in the other table.
The relation shows as a blue line. Whenever both tables appear in a query, they're joined on these fields. Select the line to see its definition, and Delete to remove it (without asking).
Dragging a field that is mapped to a unit onto another field copies the mapping instead.
There are no join types, cardinalities or foreign-key imports to set: the model's units decide.
Partitioned Data¶
When the same data is split over several physical tables – e.g. one per company with a prefix such as CRONUS$Customer – a unit can act as a partition key (see 23.4 Advanced Unit Settings). Then:
- PROJECT → Select … chooses which partitions (e.g. companies) the project uses;
- PROJECT → Get all tables lists all physical tables and views the model uses.