This is the second article in my series on the importance of developer standards when building no-code or low-code applications. If you missed the first one, you can check it out here. 

In this piece, I’ll be diving deeper into developer standards, focusing specifically on the Data Layer—a crucial foundation in any application. Whether you’re using platforms like FlutterFlow or WeWeb, maintaining standards for the Data Layer is just as important as any other part of the app to ensure its stability and scalability.

The Data Layer

The Data Layer is responsible for persisting and storing all application data, and like other layers, it must follow specific standards. Typically, the Data Layer is implemented using databases such as MySQL, PostgreSQL, or Oracle for SQL-based solutions, and Firebase or MongoDB for NoSQL solutions.

The Data Layer handles:

• Storing data like user information, product details, and order records.
• Retrieving data, such as the products in a user’s shopping cart or a customer’s order history.
• Facilitating interaction between the backend and the stored data.

In platforms like FlutterFlow, two "native" database integrations are provided: Supabase (PostgreSQL) for SQL databases and Firebase Firestore for NoSQL (document-based) storage. WeWeb also supports Supabase natively. Depending on the nature of your application, you can choose either solution.

If you'd like a deeper dive into the differences between Firebase and Supabase, check out our article, "Supabase vs Firebase: Which is Best for Your Project?"

Database Diagram

Before diving into development, one essential rule for databases is this: always create a Database Diagram first. A well-structured database diagram serves as a visual blueprint, ensuring the database design aligns with the application’s requirements. This step helps prevent potential data inconsistencies and inefficiencies down the road.

Common Standards for Naming Conventions

Consistency in naming conventions for tables and columns is key to creating a clear and understandable database schema. Here are some best practices to follow:

• Use clear, descriptive names: Table and column names should clearly indicate the data they store. Avoid using shortened or vague names.
• Avoid abbreviations and acronyms: These can cause confusion. If you must use them, make sure to document their meaning in your schema documentation.

‍SQL Databases

Let's begin with the standards for SQL databases. As mentioned earlier, we're using Supabase as our primary SQL database service.

One key practice is applying Database Normalization, at least up to the Third Normal Form (3NF). This process ensures data integrity, reduces redundancy, and improves database performance by organizing data into a structured format. By doing so, we prevent anomalies and inconsistencies in the database.

For more details on database normalization, you can read further here.

Tables and Columns

• Table Naming: Use snake_case and plural forms for table names. Examples: vehicles, user_settings, orders.
• Column Naming: Column names should also follow snake_case. Examples: role, first_name, created_at.
• Avoid Redundancy: Refrain from including the table name in column names unless necessary for clarity.
• Primary Key: Every table must have a primary key to uniquely identify each row.
• Foreign Keys: Use foreign keys to reference other tables, creating relationships and adding data validation.
• Normalization: Normalize tables to reduce redundancy, ensuring each piece of data is stored only once.
• Indexes: Create indexes on frequently accessed columns to improve query performance.
• Data Types: Choose data types carefully. For instance, use integers for IDs and strings for names to optimize performance and storage.

Optional Best Practices:

• Comments: Add comments to document the database schema, making it easier for developers to understand and maintain.
• Backups: Regularly back up your database to protect against data loss or corruption.

SQL Database Diagram Example:

You can create your database diagrams using tools like:

• dbdiagram.io
• draw.io

To keep this post concise, we'll wrap up here. In the next article, we’ll continue with Database Development Standards for NoSQL databases, focusing on Firebase Firestore.

This guide is meant to serve as a flexible starting point, not a strict set of rules. Use it to help define and tailor your own Database Development Standards to meet the specific needs of your project or organization.

‍

- Roberto Requena. Senior Developer