Best practices for database schema in DDL and SQL query generation

The Perform SQL Query by Natural Language script step and the GetTableDDL function generate Data Definition Language (DDL) that summarizes the database schema of the specified table occurrences. The underlying logic relies on field settings and the relationships graph for most of the database schema, but can also include the field comments that you enter in the Manage Database dialog as additional information.

When database schema is sent to an AI model to generate SQL queries, following these best practices helps improve the model's ability to generate useful SQL statements.

Use alphanumeric table and field names

It's best for field names to be compliant with the SQL-92 standard. In general, use alphanumeric characters; don't use special characters, except for underscore (_). Try to avoid using spaces, but you can if needed (FileMaker correctly handles table and field names containing spaces while communicating with an AI model).

Use primary key and foreign key fields

Primary key and foreign key fields in a relationship need to have the same data type—number, text, or even date or timestamp, if there's a reason to do so.

Primary key field

Any field with a unique value can be used as a primary key field. However, the best practice is to use the same criteria that FileMaker uses to automatically detect a primary key field.

To be detected, a primary key field must either be the default PrimaryKey field (or a copy of it), or meet one of the following criteria:

• use an auto-enter serial number and the following options are selected:

  • for auto-enter, Prohibit modification of value during data entry

  • for validation, Unique value

• use an auto-enter calculation that includes the Get(UUID) or Get(UUIDNumber) function and the auto-enter option Prohibit modification of value during data entry is selected

• is a stored calculation field that includes the Get(UUID) or Get(UUIDNumber) function

• use an auto-enter serial number

See Defining automatic data entry, Defining field validation, and Defining field indexing options.

Foreign key field

A foreign key field can have the same name as the corresponding primary key field in a related table, but it doesn't need to. For example, a foreign key field named fk_Contacts in the Addresses table represents a relationship from the Contacts table to the Addresses table. The best practice is to use a name that makes sense to you, because it will also be a helpful name for an AI model.

To make the purpose of the field clearer and to specify the relationship with another table, you can describe the field further in the field comments (see below). For example, add the following as a comment in the Addresses::fk_Contacts field: "[LLM] Foreign key for one-to-many relationship with Contacts table."

Note  A foreign key and its relationship to a related table are included in the DDL only if both table occurrences in the relationship are specified.

Add field comments

Field comments you enter in the Manage Database dialog are included in the DDL. To improve the model's ability to generate useful SQL statements based on the DDL, you can use the comment to explain the purpose of the field. For example, when a field is a foreign key that identifies a record in related table, or when the field's name might not be commonly understood. For a primary key field , even if FileMaker detects it and indicates in the DDL that it's a primary key, it's still a good idea identify it as such in the field comment.

See Defining and changing fields.

Add the [LLM] tag to limit fields included

Rather than include all of a table's fields in the DDL, you can specify only the fields that matter, reducing extraneous information sent to the model that can degrade the quality of the response. To do so, add the special [LLM] tag to the beginning of the field comment, then follow with a descriptive comment as needed. Any other fields in the table whose comment doesn't start with the [LLM] tag are excluded from the DDL.

For example, if the Products table includes these fields but the comment in the Status field doesn't start with the [LLM] tag:

Then the DDL for this table is:

CREATE TABLE "Products" (
"ProductID" int, /*Primary key that uniquely identifies a product*/
"ProductName" varchar(255), /*Descriptive name of the product*/
"Price" int, /*Price of the product in USD*/
PRIMARY KEY (ProductID)
);

Notice that only the three fields with the [LLM] tag are included, and the [LLM] tag itself is omitted.

Distinguish fields with the same name in multiple tables

Sometimes a database may have fields with the same name in multiple tables. For example, a Photo field in the Contacts table stores the image of customers, while another Photo field in the Orders table stores the image of order receipts. To help ensure an AI model can distinguish the different purposes of these fields, add field comments to clarify. For example, add "[LLM] Photo of customer" to the Contacts::Photo field comment and "[LLM] Photo of order receipts" to the Orders::Photo field comment.

Specify when case matters

SQL queries are case sensitive, so the results may differ depending on whether text is uppercase or lowercase. For example, a Tags field in the Products table stores the tags of each product, all in title case. In this case, to avoid unexpected query results, add "[LLM] Tags of the product, in title case" as the comment for the Products::Tags field.

Provide the valid field values

For fields that use custom value lists to specify valid field values, a best practice is to provide the valid values in a field comment so that the AI model can generate the best SQL query. For example, add "[LLM] Job title, valid values are Surgeon, Physician, Dentist, Nurse, and Pharmacist" as a field comment for the Contacts::Title field.

Don't query summary fields

The value of a summary field in a FileMaker database depends on the records in the current found set, so an SQL query may return incorrect result in some cases. Instead, use the [LLM] tag in field comments so that summary fields are excluded. SQL is sophisticated enough to perform the tasks of summary fields without including them in the DDL.

Related topics 

• Perform SQL Query by Natural Language script step

• GetTableDDL function

• Defining and changing fields