1. Objective
To design a relational database back-end for a small short-term rentals business, using an Agile development approach.
The goal is a solid database schema that supports bookings, calendars, payments, reviews, messaging, etc., for a website-based Property Management System (PMS).
2. Step-by-Step Methodology (Agile-aligned)
2.1. Define MVP Scope and Roles (Agile: Product Backlog)
Identify the core features the database must support:
Properties
Availability & Bookings
Hosts and Guests
Messaging
Payments
Define user types:
Host (owner or manager)
Guest (renter)
Admin (yourself or future superuser)
Create high-level User Stories:
“As a guest, I want to book a property, so that I can stay during my trip.”
“As a host, I want to manage my property calendar, so that I avoid double bookings.”
2.2. Identify Entities and Relationships (Agile: Sprint 0 – Architecture planning)
Create an Entity List based on user stories. Typical entities:
Users (hosts, guests, admins)
Properties
Amenities
Bookings
Calendar
Payments
Reviews
Messages / Enquiries
Draw relationships:
One host → many properties
One guest → many bookings
One booking → one property
Many-to-many: property ↔ amenities
2.3. Define Primary Keys and Foreign Keys (Database Normalisation)
Every entity gets a primary key (PK)
Use foreign keys (FK) to establish links
E.g., property_id in bookings table
Use surrogate keys (like UUID or auto-increment ID)
Avoid data duplication: follow 3rd Normal Form (3NF) unless performance says otherwise
2.4. Build the Logical Data Model (Agile: Deliverable of First Sprint)
Create a visual ERD (Entity Relationship Diagram)
Use standard notation (Crow’s foot or SSADM box-style)
Colour code:
Green = Master Data (users, properties, amenities)
Blue = Transactional Data (bookings, payments)
Purple = Others (messages, reviews)
2.5. Validate with User Stories (Agile: Sprint Review & Feedback)
Review each user story:
“Can this data model support the scenario?”
E.g., “Does the booking table include guest ID, property ID, dates, and status?”
Adjust schema if necessary
2.6. Prepare for Sprint-Based Dev Build (Agile: Incremental Development)
Export schema for developers (SQL or DBML)
Link database to front-end via REST or GraphQL API
Use iterative Agile sprints to:
Add features (e.g., cancellation handling, discounts)
Refine performance
Handle edge cases (e.g., double bookings, partial refunds)
2.7. Test, Iterate, Deploy (Agile: Sprint Cycles)
Unit test DB schema (e.g., FK constraints, NOT NULL, ENUM values)
Mock data to simulate real-world use
Start with local DB (SQLite or Postgres)
Migrate to cloud-based DBMS (AWS RDS, Firebase, Supabase)
3. Final Thoughts
This method blends Agile’s iterative design with relational database rigour. The key is modularity: build minimum core features first, then expand in layers. Each sprint delivers something usable and testable — just as Agile intends.
Master entities (green): hold reference data
- users, hosts, guests, properties, amenities
Transaction entities (blue): record activity or events
- bookings, payments, calendar, reviews
Junction Tables (purple): resolve many-to-many relationships or track system-level changes
- property_amenities, booking_events
Log or Event Entities: capture state/history
- booking_events records the lifecycle of a booking (confirmed, paid, cancelled etc.)
Cross between audit logs and system workflows. Not true “transactions”, but they are time-sensitive and often append-only
Glossary
MVP – Minimum Viable Product, the smallest functional version of our system
User Story – short statement describing what a user wants to do and why
3NF – Third Normal Form, a method of designing databases to reduce redundancy
ERD – Entity Relationship Diagram, visual map of database tables and relationships
Sprint – a short development cycle (typically 1–2 weeks) in Agile methodology
0 comments:
Post a Comment
Keep it clean, keep it lean