Supabase | Best Practices (SQL)

It’s a lot easier to spot "bad" or "not right" database schemas than it is to confirm that a database schema is right. The least we can do is build consistently following a pattern.
Bad Design Leads to Problems: Quickly causes data integrity issues. If the initial design is lacking, it's a failure at the foundation level, leading to operational constraints and potential data inconsistencies, capping the project from being able to have a smooth maintenance or new versions.
Normalization Preserves Data Quality: Normalization is a technical process needed to preserve data quality. It ensures that each fact is stored in one place (with rare exceptions), preventing inconsistencies that will arise if facts are stored in multiple places. Normalization rules specifically prevent update anomalies and data inconsistencies

Table Names

Use snake_case: user_profiles, order_items
Make names plural to represent collections: users, products, orders
Avoid prefixes like tbl* or table* - they add no value
Use descriptive, business-meaningful names

Column Names

Use snake_case: first_name, created_at, user_id
Be descriptive but concise: email_address rather than email_addr or just email
Avoid abbreviations unless they're universally understood
Use consistent naming patterns across tables

Primary Keys

Name them id for simplicity, or table_name_id for clarity: user_id, product_id
Always use the same pattern throughout your schema

Foreign Keys

Format as referenced_table_singular_id: user_id, category_id
This immediately shows the relationship and referenced table

Indexes

An efficient data schema design includes optimizing indexes. Indexing is a critical strategy for performance optimization, allowing faster data retrieval and minimal disk accesses for queries. Indexes improve the speed of locating and retrieving records by providing an ordered list of column contents. Indexes act as pointers, speeding up searches on large datasets. This is specifically important for databases like Supabase / Xano, so the query has better performance
Indexing improves query execution time, but remember that updates (INSERTs, UPDATEs, DELETEs) can be slower because indexes also need updating. Balance the use of indexes based on whether the application requires faster reads or faster writes. Build indexes to optimize specific database queries based on their frequency and importance.

Use descriptive names: idx_users_email, idx_orders_created_at_status
Include the table name and key columns in the index name

Views / Functions

Use snake_case : view_customer_data, create_customer
Avoid prefixes like vw or c - they add no value
Use descriptive, business-meaningful names
Views should have a prefix: view*

Additional Guidelines

Be consistent - pick a convention and stick to it across your entire schema
Avoid reserved words from your database system (like user, order, group)
Use meaningful abbreviations only when names would be excessively long
Consider your team - choose conventions that work for all developers
Document your conventions so new team members can follow them

Prioritize Data Integrity through Normalization and Constraints:

- Normalization is a crucial technical exercise done to preserve data quality and ensure correctness. It prevents update anomalies (mistakes) and data inconsistencies. Normalization aims to store each fact in one place, preventing inconsistencies that arise when the same fact is stored in multiple locations. Bad record layouts or designs that are not normalized, such as those containing repeating groups or combining detail and summary data, can quickly cause performance degradation and data integrity issues. Using relational databases as flat files is inefficient. The remedy for bad record design is to redesign and rebuild based on data normalization principles.

Follow this link for guidance on Normalization principals (this is a must to understand)

- SQL Constraints are rules that define what data can be stored in a table, acting as safeguards for data accuracy and integrity by enforcing validation during operations like insertions or updates. Key constraints, such as Primary Keys and Foreign Keys, are fundamental for maintaining data integrity.
  - A Foreign Key establishes relationships between tables by linking a column in one table (the child) to the primary key of another (the parent), ensuring referential integrity by preventing invalid entries.
  - The NOT NULL constraint ensures critical mandatory fields always have values.
  - The UNIQUE constraint ensures no two rows have the same value in a specified column; unlike a primary key, a table can have multiple unique constraints.
  - The DEFAULT constraint assigns a predefined value when no value is provided, ensuring consistent data.
  - The CHECK constraint enforces rules on column values based on business logic, preventing invalid data.
- Applying these constraints at the column or table level helps maintain data integrity.

Consider Physical Design

The physical implementation of the database impacts performance. As part of a good schema design, consider separating different types of data (like tabular data from text/image data) into different tables and file groups.