Search Knowledge Base Articles

Working with Database

Database design is a crucial part of developing a robust and scalable application. Here are some rules and standards your developers should follow when working on database and table creation, as well as defining relationships:

Database Design Principles

  1. Normalization

    • Follow the principles of database normalization to reduce redundancy and improve data integrity.
    • Aim for at least Third Normal Form (3NF) to ensure that the database is efficient and maintains data consistency.

  2. Database Naming Conventions

    • Use lowercase for all database, table, and column names.
    • Use underscores (_) to separate words in table and column names (e.g., user_profiles, order_items).
    • Avoid using reserved keywords as table or column names.
    • Use singular names for tables (e.g., user, order), though some prefer plural for clarity (e.g., users, orders). Choose one convention and stick to it.

  3. Primary Keys

    • Always use an id column as the primary key, which should be an auto-incrementing integer.
    • For complex cases, consider using UUIDs as primary keys if needed.

  4. Foreign Keys

    • Define foreign keys to enforce referential integrity between related tables.
    • Use descriptive names for foreign key columns (e.g., user_id, product_id).
    • Use onDelete('cascade') where appropriate to ensure that related records are deleted when a parent record is removed.

  5. Indexes

    • Create indexes on columns that are frequently used in WHERE clauses, JOINs, or as foreign keys.
    • Use composite indexes for queries involving multiple columns.
    • Avoid over-indexing, as it can negatively impact write performance.

Table Creation Standards

  1. Migrations

    • Use Laravel migrations to create and modify database tables.
    • Ensure that each migration is atomic and describes a single change to the database schema.
    • Name migration files descriptively, reflecting the action they perform (e.g., create_users_table, add_status_to_orders).

  2. Column Naming Conventions

    • Use descriptive and consistent names for columns.
    • Use created_at and updated_at for timestamp columns, which Laravel will manage automatically with $table->timestamps().
    • Use deleted_at for soft deletes with $table->softDeletes().

  3. Data Types

    • Choose appropriate data types for columns. For example, use integer for IDs, string for names, text for longer descriptions, and boolean for true/false values.
    • Use enum or tinyInteger for status fields to enforce specific values.
    • Use decimal or float for monetary values, ensuring precision.

  4. Default Values

    • Define default values for columns where applicable to avoid null values.
    • Ensure that critical columns (e.g., status, type) have meaningful default values.

Defining Relationships

  1. One-to-One Relationship

    • Implement a one-to-one relationship using the hasOne and belongsTo methods in Eloquent models.
    • Example: A User has one Profile.
    • Migration: In the profiles table, add a user_id foreign key.

// User model
public function profile()
{
    return $this->hasOne(Profile::class);
}

// Profile model
public function user()
{
    return $this->belongsTo(User::class);
}

2. One-to-Many Relationship

  • Use hasMany and belongsTo for one-to-many relationships.
  • Example: A Post has many Comments.
  • Migration: In the comments table, add a post_id foreign key.

// Post model
public function comments()
{
    return $this->hasMany(Comment::class);
}

// Comment model
public function post()
{
    return $this->belongsTo(Post::class);
}

3. Many-to-Many Relationship

  • Implement many-to-many relationships using belongsToMany in Eloquent models.
  • Use a pivot table to manage the relationship. The pivot table should have both foreign keys.
  • Example: A User belongs to many Roles, and a Role belongs to many Users.
  • Migration: Create a role_user pivot table with user_id and role_id columns.

// User model
public function roles()
{
    return $this->belongsToMany(Role::class);
}

// Role model
public function users()
{
    return $this->belongsToMany(User::class);
}

4. Polymorphic Relationships

  • Use polymorphic relationships for models that can belong to more than one type of model.
  • Example: A Comment model can be associated with both Post and Video models.
  • Migration: In the comments table, add commentable_id and commentable_type columns.

// Comment model
public function commentable()
{
    return $this->morphTo();
}

// Post and Video models
public function comments()
{
    return $this->morphMany(Comment::class, 'commentable');
}

Database Security and Integrity

  1. Data Validation

    • Validate data at both the application (Laravel validation) and database (database constraints) levels.
    • Use Laravel's form requests for validation and define validation rules within the models or services.

  2. Transactions

    • Use database transactions for operations involving multiple queries to ensure data integrity.
    • Example: When creating an order and deducting inventory, both actions should occur within a transaction to avoid inconsistencies.
DB::transaction(function () {
    // Your code here
});

  1. Avoid Raw Queries

    • Avoid using raw SQL queries unless absolutely necessary. Prefer Eloquent ORM or Query Builder for database operations to prevent SQL injection and ensure readability.
    • If raw queries are necessary, use parameter binding to prevent SQL injection.

  2. Data Encryption

    • Encrypt sensitive data before storing it in the database.
    • Use Laravel's built-in encryption functions or packages like Laravel Encryption for this purpose.


Database Migration Versioning

  1. Migration Files

    • Keep migration files under version control to track changes in the database schema.
    • Avoid modifying existing migration files after they have been applied. If changes are needed, create a new migration file.

  2. Rollback and Refresh

    • Ensure that every migration can be safely rolled back by implementing the down method.
    • Use php artisan migrate:rollback to undo migrations and php artisan migrate:refresh to reapply them.

  3. Database Seeding

    • Use seeders to populate the database with initial or testing data.
    • Keep seeders organized by category (e.g., UsersTableSeeder, ProductsTableSeeder).
    • Use factories to create fake data for testing purposes.

By adhering to these standards, your developers will be able to create a robust, scalable, and secure database structure that supports your application's needs. Let me know if you need further details or additional guidelines!

Did you find this article useful?

Related Articles