A full relational database built from scratch — from entity relationships and normalization to a live, queryable Oracle schema supporting users, restaurants, reviews, reservations, and recommendations.
Managing restaurant data, user reviews, reservations, and recommendations at any real scale quickly becomes unmanageable without structure. Without a proper relational database, data gets duplicated, relationships break down, and querying becomes unreliable.
This project tackles that problem end-to-end — starting from a business need, identifying every entity and relationship required, and building a fully normalized schema in Oracle DB that enforces data integrity by design rather than by hope.
The database was designed to handle every part of the platform — who the users are, what restaurants exist, how they're categorized, and all the interactions between them.
User profiles with unique email and phone. Credit cards linked via FK with cascading delete on user removal.
Core IdentityCompanies own multiple restaurants. Each restaurant stores location, hours, cuisine type, and activity status.
Core InventorySitDown, TakeOut, and FoodCart each extend the base Restaurant table with type-specific attributes.
Supertype/SubtypeUser-written ratings from 0.0–5.0 per restaurant, with auto-timestamping on creation.
User ContentTime-based bookings with start/end timestamps and party size. Constrained to party sizes greater than zero.
TransactionsBinary Y/N endorsements, stored separately from reviews — because recommending and reviewing are distinct actions.
EndorsementsGood database design isn't just about getting the tables right — it's about the decisions that hold the whole system together when data gets messy or scale increases.
Rather than cramming all restaurant types into one table with nullable columns, the base Restaurant table holds shared attributes while SitDownRestaurant, TakeOutRestaurant, and FoodCartRestaurant each extend it with only the fields that apply — Capacity, MaxWaitTime, and Licensed respectively. Cleaner, more normalized, and easier to query by type.
If a user account is deleted, their associated credit card records are automatically removed via ON DELETE CASCADE. This prevents orphaned payment data from persisting in the system — a data integrity decision that mirrors real-world platform behavior.
Rating is constrained to 0.0–5.0. ActivityStatus only accepts 'Active' or 'Closed'. FoodType is limited to five cuisine categories. RecommendedFlag only accepts 'Y' or 'N'. These constraints mean invalid data never enters the database, regardless of how it's inserted.
A separate Recommendation table captures binary endorsements independently of written reviews. This reflects how real platforms actually work — a user might recommend a restaurant without leaving a written review, or vice versa. Collapsing them into one table would force artificial constraints on user behavior.
The queries were designed to demonstrate real use cases — not just that the schema works, but that it can power actual product decisions.
SELECT RestaurantName, City, FoodType FROM Restaurant WHERE ActivityStatus = 'Active';
SELECT U.FirstName || ' ' || U.LastName AS Reviewer,
R.RestaurantName,
RV.Rating
FROM Review RV
JOIN Users U ON RV.UserID = U.UserID
JOIN Restaurant R ON RV.RestaurantID = R.RestaurantID;
SELECT U.FirstName || ' ' || U.LastName AS Guest,
R.RestaurantName,
RS.PartySize,
TO_CHAR(RS.StartTimestamp, 'YYYY-MM-DD HH24:MI') AS ReservationStart
FROM Reservation RS
JOIN Users U ON RS.UserID = U.UserID
JOIN Restaurant R ON RS.RestaurantID = R.RestaurantID
WHERE RS.PartySize > 2;
Constraints, cascading rules, and normalization aren't just academic — they're what stops bad data from compounding silently over time.
Separating Recommendations from Reviews wasn't a technical requirement — it was a product decision about how users actually behave on a platform.
3NF forces you to make every dependency explicit. That process of elimination is where you find the hidden assumptions baked into your original design.
The multi-table joins in the business queries were only clean and readable because the schema was designed with relationships in mind from the start.
"A database is only as powerful as
the decisions behind its design."