3.E.5: Carousell Schema Design
- 1.Know how to translate app requirements to an ERD
- 2.Know how to design SQL database schema for 2-sided marketplace like Carousell
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.
Sequelize data types are not always the same as Postgres data types. For example, the Sequelize
STRINGdata type maps to
VARCHARin PostgreSQL (and DrawSQL). Review Sequelize Data Types docs for what Postgres data types each Sequelize data type maps to.
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.
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
Userstable 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
idcolumn. All references to user IDs of buyers or sellers will reference the
Next, create a
Listingstable to enable sellers to create listings and buyers to purchase listings. The
Listingstable should contain information about the product being sold such as category, title, condition, price, description, and any other relevant information such as shipping details.
Listingsshould contain 2 foreign keys to the
Userstable, where the foreign keys can be named
Add relevant relationship lines between
Listings. Each user can have many listings as a buyer and as a seller, hence there should be 2 relationship lines between
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.
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
Conditionstables with associations to
Listingsto support cleaner data.
Conditionsonly need a single
namecolumn other than the default
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.
Every listing can have 1 or more photos. Create a
Photostable 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
listingIdforeign key that references a row (listing) in the
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.
Carousell Groups are like Facebook Groups but for Carousell. Create a
Groupstable that stores group name. Create tables to represent a M-M relationship between
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.
Export your completed ERD as an image in File > Export in DrawSQL and share it in your section Slack channel.