Database Schema

# Schema for PostgresQL using SQLAlchemy ORM

Users

column name	data type	details
id	integer	not null, primary key
name	string	not null
email	string	not null, unique
hashed_password	string	not null
created_at	datetime	not null
updated_at	datetime	not null

• unique constraint on email
• SQLAlchemy Dreams relationship
• SQLAlchemy Fragments relationship

Dreams

column name	data type	details
id	integer	not null, primary key
title	string	unique
notes	text
keywords	string
dreamer_id	integer	not null, foreign key
created_at	datetime	not null
updated_at	datetime	not null

• dreamer_id references Users table as a foreign key
• unique constraint on title
• SQLAlchemy Users relationship
• SQLAlchemy Fragments relationship via Dreams_Fragments table

Fragments

column name	data type	details
id	integer	not null, primary key
title	string
setting	string
emotions	string
description	text
user_id	integer	not null, foreign key
createdAt	datetime	not null
updatedAt	datetime	not null

• user_id references Users table as a foreign key
• SQLAlchemy Users relationship
• SQLAlchemy Dreams relationship via Dreams_Fragments table

Dreams_Fragments (JOINS Dreams & Fragments)

column name	data type	details
id	integer	not null, primary key
dream_id	integer	not null, foreign key
fragment_id	integer	not null, foreign key
createdAt	datetime	not null
updatedAt	datetime	not null

• dream_id references Dreams table as a foreign key
• fragment_id references Fragments table as a foreign key
• SQLAlchemy Dreams relationship
• SQLAlchemy Fragments relationship

Images

column name	data type	details
id	integer	not null, primary key
image_url	string	not null
createdAt	datetime	not null
updatedAt	datetime	not null

• SQLAlchemy Fragments relationship via Fragments_Images table

Fragments_Images

column name	data type	details
id	integer	not null, primary key
fragment_id	integer	not null, foreign key
image_id	integer	not null, foreign key

• fragment_id references Fragments table as a foreign key
• image_id references Images table as a foreign key
• SQLAlchemy Fragments relationship
• SQLAlchemy Images relationship

Visual Representation of Schema