EasyBackendDesign

Design a Bookmark Schema

You're designing the data layer for a bookmark app. Users save URLs as bookmarks. They can group bookmarks into folders. A bookmark can live in multiple folders at once — saving the same article into both 'Reading List'...

Start problem Browse all problems

What you will practice

SQLDatabase / SQLSchema DesignForeign KeysMany-to-Many

Requirements

Create a `users` table with at least `id INTEGER PRIMARY KEY` and `email TEXT NOT NULL`.
Create a `bookmarks` table with at least `id INTEGER PRIMARY KEY`, `user_id INTEGER NOT NULL REFERENCES users(id)`, `url TEXT NOT NULL`, and `title TEXT NOT NULL`.
Create a `folders` table with at least `id INTEGER PRIMARY KEY`, `user_id INTEGER NOT NULL REFERENCES users(id)`, and `name TEXT NOT NULL`.
Create a `bookmark_folders` join table linking `bookmark_id` and `folder_id`, each declared with `REFERENCES` to enforce integrity. A single bookmark may appear in multiple folders.
Create a `folder_shares` table with `folder_id` and `user_id`, each declared with `REFERENCES`, to model a folder being shared with another user.
All foreign keys must be declared with `REFERENCES <table>(id)` so SQLite enforces them when `PRAGMA foreign_keys = ON` is set.

Starter files

schema.sqlEditable starter

What the judge checks

Runs in the python environment with the python-pytest runner.
Uses a 5000ms judge budget.
Behavior rules include: Schema Executes, Core Tables Exist, Bookmark To Folder Is Many To Many, Folder Sharing Supported.

Constraints

Do not store folder IDs as a comma-separated list on bookmarks. Use a real join table.
The schema must execute with `conn.executescript(...)` — DDL only, no DML.
Foreign keys must be declared inline with `REFERENCES`. The judge will not configure them externally.

Example behavior

Input

INSERT INTO users (id, email) VALUES (1, 'a@x.com');
INSERT INTO bookmarks (id, user_id, url, title) VALUES (1, 1, 'https://example.com', 'Example');
INSERT INTO folders (id, user_id, name) VALUES (1, 1, 'Reading List'), (2, 1, 'Inspiration');
INSERT INTO bookmark_folders (bookmark_id, folder_id) VALUES (1, 1), (1, 2);
SELECT COUNT(*) FROM bookmark_folders WHERE bookmark_id = 1;

Output

The same bookmark can live in multiple folders simultaneously — that's what the join table buys you.

Input

INSERT INTO users (id, email) VALUES (2, 'b@x.com');
INSERT INTO folder_shares (folder_id, user_id) VALUES (1, 2);
SELECT user_id FROM folder_shares WHERE folder_id = 1;

Output

User 2 now has access to folder 1, even though user 1 owns it.

Follow-up

How would you let a shared folder be either read-only or editable for the recipient? What changes in `folder_shares`?