3.ICE.9: DB Schema Design - Carousell

Search…

Introduction
Design a database schema for Carousell.
Base
ERD
Create a DB schema for Carousell by drawing an ERD for the DB, starting with only essentials for Carousell's listing and selling functionality. The real Carousell DB would have many tables, but we don't need to include everything for this exercise. In our ERD, name the tables and columns, identify data types, and define any relationships between the tables.
One trick you may encounter in this exercise is that a product listing can be associated with both a seller and a buyer, but both seller and buyer foreign keys can reference the same users table. From this we learn that a single table can have more than 1 association with the same other table.
Feel free to use the Zoom Whiteboard for initial sketches. For more professional-looking ERDs, consider using the Entity Relation features of free drawing software draw.io, or the free ERD visualisation software dbdiagram.io. The latter requires us to learn a custom ERD-creation syntax, but it should be relatively straightforward.
DB Setup
Create a new local database called carousell.
CREATE TABLE Statements
Write and execute CREATE TABLE statements to create all tables and relationships in the ERD. Record these statements in a file init_tables.sql.
INSERT Statements
Write and execute INSERT statements to insert dummy data into the tables. Record these statements in a file seed.sql.
SELECT Statements
Write and execute sample SELECT queries you might need to render a given page on Carousell, for example this listings page. Don't worry about INSERT or UPDATE queries, or queries for searches. Record the SELECT statements in a file queries.sql.
More Comfortable
Add tables and columns for additional Carousell features and repeat Base instructions.