Thinking Like a Database Designer

Thinking like a database designer

Before CREATE TABLE ask: what entities exist, how are they linked, and what does the app do most often? For a hotel: free rooms, check-in, billing, staff reports.

Derive entities from the business — guest, room, reservation, payment, employee. An attribute belongs to one entity only. In hotel_db reception, finance, and housekeeping share gosti, sobe, rezervacije, placanja, and zaposleni — every change must stay consistent for all app modules.

In depth

Design queries first — indexes and keys follow usage. The ER diagram evolves via migrations, not panic ALTER in production. In Workbench run the sample SQL on test data before production, check EXPLAIN as tables grow, and document expected results for teammates.

Key points

• Entities from business flow. — hotel_db example.
• No duplicated attributes. — hotel_db example.
• Queries drive indexes. — hotel_db example.
• ER before SQL. — hotel_db example.
• Migrations over panic changes. — hotel_db example.

Common mistake

Starting with CREATE TABLE without a model because you will fix it later. Typical fallout: mismatched reception vs finance data, lost reservations, or blocked check-ins at peak season — always backup before production DDL/DML.

Summary

A good hotel_db model saves months of production refactoring. Practice again on hotel_db until you can explain every result row and tie it to hotel workflows (check-in, billing, reporting).

Note: Tip: keep versioned .sql scripts (hotel_schema.sql, seed.sql) in Git — reproducibility matters when several people work on the same hotel_db model.