3.E.5: Carousell Schema Design

Learning Objectives

1. Know how to translate app requirements to an ERD

2. Know how to design SQL database schema for 2-sided marketplace like Carousell

Introduction

We will create a database ERD for Carousell, a 2-sided buyer and seller marketplace. We will still only use 1-M and M-M relationships, but we will see different ways to associate tables with a different use case.

Setup

DrawSQL

If you haven't already, create a DrawSQL account and create a new diagram for this exercise. Choose PostgreSQL when asked to choose Database Type.

Review Postgres data types

Sequelize data types are not always the same as Postgres data types. For example, the Sequelize STRING data type maps to VARCHAR in PostgreSQL (and DrawSQL). Review Sequelize Data Types docs for what Postgres data types each Sequelize data type maps to.

Base: Users Buy and Sell Products with Listings

Define minimum necessary functionality

Always start by designing a schema for the minimum necessary functionality. In this case, we want sellers to be able to list products and buyers to be able to buy products. For now, we will assume payment takes place offline.

Consider how to minimise duplicate data

Naively we could have separate tables for buyers and sellers, but if we look closely much of their information will be the same, for example contact information. It is also a common pattern on Carousell for users to be both buyers and sellers, and if we keep all buyer and seller data in separate tables, we would duplicate data for users that are both buyers and sellers. Duplicating data is strongly discouraged in SQL schema design.

To keep things simple, create a single Users table to represent both buyers and sellers. Users should have first name, last name, phone number, email and password (to store an encrypted copy of their password, not plain text) in addition to the default id column. All references to user IDs of buyers or sellers will reference the Users table.

Create Listings

Next, create a Listings table to enable sellers to create listings and buyers to purchase listings. The Listings table should contain information about the product being sold such as category, title, condition, price, description, and any other relevant information such as shipping details. Listings should contain 2 foreign keys to the Users table, where the foreign keys can be named buyerId and sellerId respectively.

Add relevant relationship lines between Users and Listings. Each user can have many listings as a buyer and as a seller, hence there should be 2 relationship lines between Users and Listings. Each listing can only have 1 buyer and 1 seller. For now we assume sellers can only list 1 stock item in each listing.

Create Categories and Conditions tables for cleaner data

Having categories and conditions data as string columns can yield messy data, because sellers can input different strings that represent the same categories or conditions. This is fine for a simple solution, but a more robust solution to support cleaner category and condition data would have separate tables for categories and conditions, and only allow sellers to choose from a fixed list of categories and conditions for each listing. There would be 1-M relationships between Categories and Listings and between Conditions and Listings.

Implement Categories and Conditions tables with associations to Listings to support cleaner data. Categories and Conditions only need a single name column other than the default id column.

Good job!

If we were to create Carousell, we would now have more confidence in creating our models and migrations. At this stage in app development, there is no need to have 100% clarity on the non-relationship columns in our tables because we can always add them later, and chances are they will change as we develop and use our app.

Comfortable: Add photos to listings

Every listing can have 1 or more photos. Create a Photos table that stores photos for each listing, where each row (photo) has a link to the photo and index of the photo (to represent photo order on the listing, 1st photo is cover image). Each photo should also have a listingId foreign key that references a row (listing) in the Listings table.

We store links to photos and not full files in SQL databases because files can slow our databases down unnecessarily. Instead we use file hosting services like Firebase Storage and Amazon S3 and store links to our images in SQL.

More Comfortable: Carousell Groups

Carousell Groups are like Facebook Groups but for Carousell. Create a Groups table that stores group name. Create tables to represent a M-M relationship between Groups and Users, such that users can have many groups and vice versa. Every group can have 1 or more group admin, where admin status can be represented as a column in the junction table between users and groups.

Submission

Export your completed ERD as an image in File > Export in DrawSQL and share it in your section Slack channel.

Reference Solution

Here is a reference solution for the Base exercise. You can do better!