If you have specified the path to both a subject table and a linked table in Step 4, then you can click on the Relationships tab to specify how the two tables are linked. This tab is not present if you’ve only specified a single subject table.
For subject table-linked table datasets, you can only specify so-called one-to-many relationships. That is to say, an entry in the subject table can be associated with one or more entries in the linked table. For example, each customer can have many shopping cart items. Or, each bank account can have many transactions.
MOSTLY AI establishes this relationship using a primary key and a foreign key:
-
The
primary keyrefers to a column in your subject table that contains unique identifiers for your subjects. -
The
foreign keyis its counterpart in your other tables. You can use the identifiers in the subject table to link to rows in these tables.
The below image shows a usage example of these keys. The subject table players.csv has a primary key called id, and the linked table has a foreign key called players_id. The unique identifier 00020a493f3b is marked in green. This identifier links the baseball player Jorge Lopez with his four events in the seasons.csv table.
|
|
MOSTLY AI automatically links two tables if the subject table contains a column called id and the first columns of the second table contains _id in its name (for instance, players_id).
You can also specify the primary and foreign keys.
To edit the primary key, click on the table in the first row.
This opens a drawer where you can pick a primary key from the Primary key drop-down menu.
Click Save when done editing.
To edit the foreign key, click on the left-most table in the second row.
This opens a drawer where you can pick a foreign key from the Foreign key drop-down menu.
Click Save when done editing.
