We recommend the following best practices to keep your database clear, consistent, and reliable. They help avoid invalid relations, improve readability, and make scripts easier to maintain.

Prevent invalid relations

To avoid invalid relations when creating your tables, ensure that foreign key references are properly constrained to existing values in the related table. This helps maintain data integrity and prevents mismatches during ingestion.

For example:

CREATE TABLE [dbo].[Recipes] [CuisineID] NVARCHAR (200) NOT NULL REFERENCES dbo.Cuisine

This constraint ensures that any value entered into CuisineID must already exist in the Cuisine table.

Column Naming and Order

We recommend naming the primary key column after the table, e.g., RecipeID for the Recipe table. For consistency and clarity:

  • Place the object ID as the first column (e.g., RecipeID)
  • Place the parent ID as the second column (e.g., CuisineID)
  • Use numeric types (INT) for IDs whenever possible

Script

For clarity and better organization, we recommend splitting your script into two sections:

  • Schema creation (all CREATE TABLE statements)
  • Data insertion (all INSERT INTO statements)

This structure improves readability and makes it easier to manage or debug your ingestion process.

See the example script for reference.