Best Practices for Enriching Your Database Schema

For optimal AI enrichment of your database schema, adhere to the following best practices. These recommendations improve an LLM’s contextual understanding, resulting in more precise and reliable SQL query generation.

• Use clear language

  Write all annotations in clear, domain-specific English to accurately express the business meaning of each database object.

• Write effective descriptions

  Create an annotation using the DESCRIPTION label to clarify the purpose of a database object, like a table or column. For example, you can annotate a table named T_EMP as follows to describe its role:

  This table stores both active and former employees, including contractors, with one row per employee.

• List aliases for database objects

  Use the ALIASES label to provide common synonyms or alternative names for a database object. Because users often refer to objects using various synonyms, the ALIASES annotation helps the LLM map these variations accurately for a particular object, improving its ability to interpret user intent. For example, a column named EMP_ID may be referred to as employee number, person number, or worker id. You can list these alternatives in an annotation as follows:

  Common aliases for this column include employee number, worker id, and person number.

• Specify measurement units for numeric data

  For columns containing numeric data, use the UNITS annotation to specify the unit of measurement. This prevents the LLM from misinterpreting values. For example, you can annotate a salary_amount column as: Expressed in United States Dollars (USD). This ensures the model correctly identifies the currency type. Similarly, for a distance column, you can annotate Units: kilometers to ensure the model performs accurate conversions or aggregations.

• Define the join logic

  When joining tables, use the JOIN COLUMN label to specify preferred join partners for a column. Because automated SQL generation with LLMs often struggles with join logic, this annotation guides the model toward the correct join. For example, you can annotate a column named EMPLOYEE.DEPT_ID as follows:

  This column is most commonly joined with DEPARTMENT.DEPT_ID and represents the employee's home department.

• Enumerate sample values

  Use the VALUES label to provide the LLM with example or distinct values for a column. This information helps the model recognize valid predicates and directly improves its filtering capabilities. For example, you can annotate a column named status_code as follows:

  Typical values for status_code include A (active), I (inactive), and T (terminated).

• Group related tables

  Organize tables into groups that align with your application domains and annotate the groups. By grouping tables, you help the LLM understand relationships that aren’t explicitly defined by foreign keys. For example, you can organize all tables related to payroll into a group named HR_Payroll and annotate it as follows:

  This group contains all tables related to payroll.