What is Database Design?

Database design is the process of structuring a database schema to efficiently store, manage, and retrieve data while minimizing redundancy and ensuring relationships between entities are properly maintained.

Key Principles of Database Design

1. Avoid Data Duplication (Normalization)
   • Ensure the same data isn’t repeated unnecessarily across multiple records.
   • Example:
     • Instead of storing city and state with every user record:
       • Cities Table: city_id, city_name, state
       • Users Table: user_id, user_name, city_id
   • Use relationships via IDs (foreign keys) to connect tables.
2. Establish Clear Relationships Between Tables
   • Tables should be related logically.
   • Queries should be optimized based on these relationships.
   • Helps maintain data integrity and avoid inconsistencies.
3. Design with Real-World Scenarios in Mind
   • Always think about actual use cases:
     • Can a user book multiple rooms?
     • Can multiple users review the same hotel?
     • How to handle availability of different room categories?

Booking.com Example — Requirements

Requirement Analysis:

• Hotel Owners should be able to list properties and rooms.
• Users should be able to:
  • Book hotel rooms for specific dates.
  • Bookmark (wishlist) hotels.
  • Write reviews for hotels.

Database Entities and Relationships

Entities:

Entity-Relationship Mapping

Notation:

• 1 : N = One to Many
• N : 1 = Many to One
• M : N = Many to Many