= Relational Design for ChapterX =

== Notation ==

* Primary keys are bolded and underlined.
* Foreign keys are marked with `*` at the end of their name and the referenced entity is written in parentheses.
* Complex attributes are bolded, and their containing attributes are following them, made italic.
* Multivalued attributes have their own table

== Tables ==

1. **USER** (**__{{{user_ID}}}__**, {{{username}}}, {{{email}}}, {{{user_name}}}, {{{surname}}}, {{{password}}}, {{{user_created_at}}}, {{{user_updated_at}}})

2. **ADMIN** (**__{{{user_ID*}}}__** (USER), {{{assigned_at}}})

3. **REGULAR_USER** (**__{{{user_ID*}}}__** (USER), {{{joined_at}}})

4. **WRITER** (**__{{{user_ID*}}}__** (USER), {{{bio}}})

5. **STORY** (**__{{{story_ID}}}__**, {{{title}}}, {{{mature_content}}}, {{{short_description}}}, {{{image}}}, {{{story_content}}}, {{{status}}}, **__{{{user_ID*}}}__** (WRITER), {{{story_created_at}}}, {{{story_updated_at}}})

6. **CHAPTER** (**__{{{chapter_ID}}}__**, {{{chapter_number}}}, {{{chapter_name}}}, {{{title}}}, {{{chapter_content}}}, {{{word_count}}}, {{{rating}}}, {{{published_at}}}, {{{view_count}}}, **__{{{story_ID*}}}__** (STORY), {{{chapter_created_at}}}, {{{chapter_updated_at}}})

7. **GENRE** (**__{{{genre_ID}}}__**, {{{genre_name}}})

8. **READING_LIST** (**__{{{list_ID}}}__**, {{{list_name}}}, {{{list_content}}}, {{{is_public}}}, **__{{{user_ID*}}}__** (USER), {{{list_created_at}}}, {{{list_updated_at}}})

9. **NOTIFICATION** (**__{{{notification_ID}}}__**, {{{notification_content}}}, {{{content_type}}}, {{{is_read}}}, {{{link}}}, **__{{{user_ID*}}}__** (USER), {{{story_ID*}}} (STORY), {{{notification_created_at}}})

10. **AI_SUGGESTION** (**__{{{suggestion_ID}}}__**, {{{original_text}}}, {{{suggested_text}}}, {{{suggestion_type}}}, {{{accepted}}}, {{{suggestion_created_at}}}, {{{applied_at}}}, **__{{{story_ID*}}}__** (STORY))

11. **LIKE** (**__{{{user_ID*}}}__** (USER), **__{{{story_ID*}}}__** (STORY), {{{liked_at}}})

12. **COMMENT** (**__{{{comment_ID}}}__**, {{{comment_content}}}, **__{{{user_ID*}}}__** (USER), **__{{{story_ID*}}}__** (STORY), {{{comment_created_at}}}, {{{comment_updated_at}}})

13. **COLLABORATION** (**__{{{user_ID*}}}__** (USER), **__{{{story_ID*}}}__** (STORY), {{{role}}}, {{{permission_level}}}, {{{collab_created_at}}})

14. **HAS_GENRE** (**__{{{story_ID*}}}__** (STORY), **__{{{genre_ID*}}}__** (GENRE))

15. **READING_LIST_ITEMS** (**__{{{list_ID*}}}__** (READING_LIST), **__{{{story_ID*}}}__** (STORY), {{{added_at}}})

16. **NEED_APPROVAL** (**__{{{suggestion_ID*}}}__** (AI_SUGGESTION), **__{{{story_ID*}}}__** (STORY), **__{{{chapter_ID*}}}__** (CHAPTER))

=== DDL script for creation and deletion of tables ===

[wiki:ddlScript.sql DDL script]

=== DML script for inserting data in the tables ===


[wiki:dmlScript.sql DML script]

=== Relational diagram made in DBeaver ===

[[Image(relational_schema (2).png​)]]