Relational Structure
Database ERD
The bookstore schema is displayed below with sharp relationship lines and a clean academic-style structure.
Public Tables: profiles, authors, categories, books, favorites
Relations: author → books, category → books, user → favorites
AUTHORS
id PKBIGINT
nameTEXT
nationalityTEXT
birth_yearINT
biographyTEXT
created_atTIMESTAMP
CATEGORIES
id PKBIGINT
nameTEXT
slugTEXT
created_atTIMESTAMP
BOOKS
id PKBIGINT
author_id FKBIGINT
category_id FKBIGINT
titleTEXT
isbnTEXT
publish_yearINT
pagesINT
languageTEXT
format_typeTEXT
short_descriptionTEXT
descriptionTEXT
cover_urlTEXT
is_publishedBOOLEAN
created_atTIMESTAMP
updated_atTIMESTAMP
PROFILES
id PKUUID
full_nameTEXT
emailTEXT
addressTEXT
avatar_urlTEXT
bioTEXT
roleTEXT
created_atTIMESTAMP
updated_atTIMESTAMP
FAVORITES
id PKBIGINT
user_id FKUUID
book_id FKBIGINT
created_atTIMESTAMP
Schema Notes for Presentation
profiles
Stores each authenticated user’s public information such as full name, email, address, avatar, biography, and role.
authors
Stores author identity details and biography information used on author and book detail views.
categories
Defines genres and catalog groups so books can be filtered and searched by category.
books
The main catalog table. Each book connects to one author and one category and contains all display content.
favorites
Creates the saved-books relation between a user profile and the books selected by that user.