Skip to main content
Problem 29

Design a Bookmark Schema

EASYDESIGN
Database / SQL+3

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' and 'Inspiration' is normal. Folders can also be shared with other users so they show up in someone else's account too.

Write the SQL schema. The judge will run your schema.sql against an in-memory SQLite database, insert fixture data, and verify the relationships work — including foreign-key integrity and the many-to-many between bookmarks and folders.

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.
Examples
Example 1
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
2
Note

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

Example 2
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
2
Note

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

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.
Follow-up

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

Hints
Related Practice
Track
System Design

Keep moving through related system design problems and build a stronger search-friendly practice loop around this topic.

View track →
Console output will appear here...