SmartNote Database Design from user and note to note_tag

I started SmartNote from database design because schema choices define most backend boundaries early.

Core tables#

Current core tables:

• user
• note
• tag
• note_tag
• favorite

These are enough to support the first product loop.

user#

Stores registration and login identity.

Key points:

• id as primary key
• username, password, email for auth flow
• create_time, update_time for auditing

note#

Main business table.

Key points:

• user_id binds ownership
• title and content store core data
• is_deleted enables soft delete
• timestamps support sorting and tracking

Relationship:

• one user to many notes

tag#

Stores tag definitions. The table is intentionally simple.

note_tag#

Bridge table for many-to-many between notes and tags.

Why this is required:

• one note can have many tags
• one tag can belong to many notes

This is also a common interview topic, so it is useful to implement and explain clearly.

favorite#

Represents user-note favorite relationships.

This is effectively another relation table with business meaning.

Design principles used in this version#

1. Solve core product flow first

• registration/login
• note management
• tag classification
• search and favorites

2. Keep expansion room

• view counts
• hot-note ranking
• search history
• richer user profile fields

3. Keep migration-friendly boundaries

• easier transition from monolith to simple microservices
• user and note domains are already separable

Closing#

This schema is a foundation, not an endpoint. The next step is to map it into entities, services, and APIs, then iterate with real usage feedback.