SQL Resources

SQL Database Design and Best Practices

SQL Database Design

1. Normalization:

   • 1NF (First Normal Form): Ensure tables have atomic columns and unique records.
   • 2NF (Second Normal Form): Remove partial dependencies by ensuring all non-key attributes depend on the entire primary key.
   • 3NF (Third Normal Form): Remove transitive dependencies, ensuring non-key attributes depend only on the primary key.

2. Primary Keys:

   • Use unique, non-null identifiers for each record.
   • Surrogate keys (e.g., auto-increment integers) are often preferred for simplicity.

3. Foreign Keys:

   • Define foreign keys to enforce referential integrity and establish relationships between tables.

4. Indexes:

   • Create indexes on frequently queried columns to enhance performance.
   • Avoid excessive indexing to prevent negative impacts on write performance.

5. Data Types:

   • Select appropriate data types for efficiency and accuracy.
   • Prefer VARCHAR over CHAR for variable-length strings to save space.

6. Constraints:

   • Apply constraints (e.g., NOT NULL, UNIQUE) to maintain data accuracy and integrity.

7. Views:

   • Use views to simplify complex queries and encapsulate business logic.

Best Practices

1. Consistent Naming Conventions:

   • Use clear and consistent names for tables, columns, and indexes to improve readability and maintainability.

2. Documentation:

   • Maintain detailed documentation of the schema, relationships, and business rules for better understanding and future modifications.

3. Backup and Recovery:

   • Regularly back up your database and test recovery procedures to safeguard against data loss.

4. Security:

   • Implement proper user roles and permissions to control access.
   • Use encryption for data at rest and in transit to protect sensitive information.

5. Performance Tuning:

   • Continuously monitor and optimize queries and indexes.
   • Analyze performance metrics to identify and address bottlenecks.

6. Version Control:

   • Track schema changes with version control systems to manage modifications and collaborate effectively.

7. Data Integrity:

   • Regularly validate and clean data to maintain consistency and reliability.

8. Scalability:

   • Design with scalability in mind to accommodate growth. Consider partitioning and sharding for handling large data volumes.

SQL Practice Problems

• LeetCode Problems:

  • Department Top Three Salaries
  • Trips and Users
  • Human Traffic of Stadium
  • Median Employee Salary
  • Game Play Analysis V
  • User Purchase Platform
  • Market Analysis II
  • Tournament Winners
  • Report Contiguous Dates
  • Sales by Day of the Week
  • Leetcodify Similar Friends

• HackerRank SQL Practice Problems:

  • 15 Days of Learning SQL
  • Draw The Triangle 1
  • Draw The Triangle 2
  • Print Prime Numbers
  • Interviews
  • Binary Tree Nodes
  • New Companies
  • The Report

MongoDB Resources

• MongoDB Instagram Post