designing database tables

overview

always have primary key
- even when tables have one-to-one mapping, better to avoid tight coupling
foreign keys

related data should be stored in separate datas and combined with joins

Identify core domain concepts that your app deals with, for ex:
- user
- photo
- tag
- note
Identify core verbs, and mappings between nouns
- User uploads Photos
- Photo has 1+ Notes
- Photo has 0+ Tags
Define entities and their attributes
- User
  - id
  - name
  - created_at
- Photo
  - id
  - user_id
  - storage_path
  - public_url
  - title
  - caption
  - note_id
  - embedding
  - created_at
  - updated_at
- Note
  - id
  - photos_id
  - user_id
  - text
  - created_at
- Tags
  - id
  - user_id
  - name
- Photo Tags
  - photo_id
  - tag_id
  - PK (photo_id, tag_id)
Consider efficiency of queries

Query	Implementation
”Find similar photos”	vector similarity search
”Search by text”	full-text search on photo.caption
”show photos with ‘art reference’ tag”	join `Photo Tags` with `Tags`