Two-table relationship

If you use the web interface, add both tables to the generator and set their relationship.

Steps

1. In MOSTLY AI, select Generators from the top menu.
2. On the Generators page, click + New generator.
3. Upload the customers table.
   1. Click Upload file.
   2. Upload the customers table, leave its name as customers, and click Proceed.
      Step result: The table is now added to the generator.
4. Upload the purchases table.
   1. In the generator, click Add table.
   2. Click Upload files, upload the table, leave its name as purchases, and click Proceed.
      Step result: The table is now added to the generator.
5. Set the relationship between the customers and purchases tables.
   1. Expand the purchases table.
   2. Expand Table relationships.
   3. Click Modify.
   4. In the pop-up, click Add foreign key.
   5. For Foreign key, select the foreign key column for the purchases table. In this case, the users_id column.
   6. For Referenced table, select the table to which the foreign key points to. In this case, the customers table.
   7. For Referenced primary key, select the primary key column of the referenced table. In this case, the id column.
   8. Select the Is context checkbox.
   9. Click the confirmation button.
6. Click Close in the upper right to close the drawer.

Result

The two-table relationship foreign key is now set. The linked table purchases is now nested and indented under its subject table customers.

With the context relationship defined, the generator can capture the correlations and retain the referential integrity between the two tables during AI model training.

Optionally, you can click Relationship diagram to get an overview of all table relationships.

What’s next

You can now train the generator on the CDNOW dataset with the default options. To do so, click Configure models and then Start training.

With the Python SDK, define each table as a dictionary in the tables list and include the primary_key and the foreign_keys keys for the subject and linked table. It is important to set is_context: True for the foreign key. For more information, see Context foreign key.

# load data
import pandas as pd
cdnow_url = "https://github.com/mostly-ai/public-demo-data/raw/dev/cdnow/"
users_df = pd.read_csv(cdnow_url + "users.csv.gz")
purchases_df = pd.read_csv(cdnow_url + "purchases.csv.gz")
 
# create and train a generator
from mostlyai.sdk import MostlyAI
mostly = MostlyAI(api_key="INSERT_API_KEY")
config = {
    "name": "CDNOW",
    "tables": [
        {
            "name": "users", 
            "data": users_df,
            "primary_key": "id"
        },
        {
            "name": "purchases",
            "data": purchases_df,
            "foreign_keys": [{"column": "users_id", "referenced_table": "users", "is_context": True}]
        }
    ]
}
g = mostly.train(config=config, start=True, wait=True)
g