defog/sqlcoder-7b-2 · Is using the database schema the best way to describe prime the model?

Feb 7

I have a use case where there's no DDL, just a description of the tables, with provided data types, and descriptions of all of the columns, in JSON.

I could generate DDL statements, of course. But wondering if this is the best way to hint at the model of the database structure.

Also, how would you go about supplying the very verbose descriptions of all of the data types? Would SQL comments be best? Postgres-style column comments?

Thanks!

rishdotblog

Defog.ai org Feb 7

The model is optimized for DDL statements – though YMMV with JSON descriptions!

And yes, SQL comments for column description work best! Example here! Also copying in full below. The join hints are not strictly required, but do improve performance if you have a very complex table.

CREATE TABLE products (
  product_id INTEGER PRIMARY KEY, -- Unique ID for each product
  name VARCHAR(50), -- Name of the product
  price DECIMAL(10,2), -- Price of each unit of the product
  quantity INTEGER  -- Current quantity in stock
);

CREATE TABLE customers (
   customer_id INTEGER PRIMARY KEY, -- Unique ID for each customer
   name VARCHAR(50), -- Name of the customer
   address VARCHAR(100) -- Mailing address of the customer
);

CREATE TABLE salespeople (
  salesperson_id INTEGER PRIMARY KEY, -- Unique ID for each salesperson 
  name VARCHAR(50), -- Name of the salesperson
  region VARCHAR(50) -- Geographic sales region 
);

CREATE TABLE sales (
  sale_id INTEGER PRIMARY KEY, -- Unique ID for each sale
  product_id INTEGER, -- ID of product sold
  customer_id INTEGER,  -- ID of customer who made purchase
  salesperson_id INTEGER, -- ID of salesperson who made the sale
  sale_date DATE, -- Date the sale occurred 
  quantity INTEGER -- Quantity of product sold
);

CREATE TABLE product_suppliers (
  supplier_id INTEGER PRIMARY KEY, -- Unique ID for each supplier
  product_id INTEGER, -- Product ID supplied
  supply_price DECIMAL(10,2) -- Unit price charged by supplier
);

-- sales.product_id can be joined with products.product_id
-- sales.customer_id can be joined with customers.customer_id 
-- sales.salesperson_id can be joined with salespeople.salesperson_id
-- product_suppliers.product_id can be joined with products.product_id

rishdotblog changed discussion status to closed Feb 13