Explore

Database modeling

Course Material

⁠

DATABASE DESIGN - 101.pdf

2 MB

⁠

🟢 Level 1

Design a database for a country that sells pears and apples to other countries.

Pears and apples have the same price for all countries.

Each country orders several times a year.

🟠 Level 2 Hotel Chain Database Modeling

Context

You are a No Code Automation Specialist hired by the Event Manager of a hotel chain to design a centralized database system. The chain operates multiple hotel properties across different cities and wants to consolidate information currently scattered across emails, spreadsheets, and various tools.

Business Requirements

The hotel chain needs to:

Manage multiple hotel properties (each with a name, location, and city)

Track available spaces/rooms in each hotel (ballrooms, conference rooms, meeting rooms with capacity, amenities like WiFi, catering facilities, AV equipment)

Record client information (company name, contact person, email, phone, address, booking history)

Track event bookings (date, time, duration, which space is booked, which client booked it, number of attendees, special requests)

Manage services that can be added to events (catering, audiovisual equipment, parking, decoration, security – each with a base price)

Monitor space availability (which spaces are free on which dates/times)

References :

Notion FAQ page :

Gaïa Hotels FAQ⁠

⁠

Locations & Room Pricing :

Gaïa Hotels - Locations and pricing.xlsx⁠

⁠

Bookings :

https://airtable.com/applvzJaL0e9iHj1h/shr9j3gE3YNZrXYTU⁠

⁠

Your Task

Design a normalized database schema that solves these problems. Include:

Identify the entities (main objects) your database needs

Define attributes for each entity (what information do you need to store?)

Identify relationships between entities (one-to-many, many-to-many, etc.)

Draw an Entity-Relationship Diagram (ERD) showing your schema

Explain why your design allows the chain to answer questions like:

Which client booked the most events in the last 6 months?

What services were included in event X?

Is space Y available on date Z?

What is the total revenue from client A?

Implement your newly designed database in Airtable.

Deliverables

A clear Entity-Relationship Diagram (hand-drawn, Whimsical, or any tool. A short explanation (2-3 sentences per entity) describing the purpose of each table and its key relationships.

A link to your Airtable base

⁠

Want to print your doc?
This is not the way.

Try clicking the ··· in the right corner or using a keyboard shortcut (

CtrlP

) instead.