= 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}}}, {{{name}}}, {{{surname}}}, {{{password}}}, {{{created_at}}}, {{{updated_at}}})

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

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

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

5. **STORY** (**__{{{story_ID}}}__**, {{{mature_content}}}, {{{short_description}}}, {{{image}}}, {{{content}}}, **__{{{user_ID*}}}__** (WRITER), {{{created_at}}}, {{{updated_at}}})
   * status (multi-valued attribute, see table **STATUS**)

6. **STATUS** (**__{{{story_ID*}}}__** (STORY), **__{{{status}}}__**)

7. **CHAPTER** (**__{{{chapter_ID}}}__**, {{{chapter_number}}}, {{{chapter_name}}}, {{{title}}}, {{{content}}}, {{{word_count}}}, {{{rating}}}, {{{published_at}}}, {{{view_count}}}, **__{{{story_ID*}}}__** (STORY), {{{created_at}}}, {{{updated_at}}})

8. **GENRE** (**__{{{genre_ID}}}__**, {{{name}}})

9. **READING_LIST** (**__{{{list_ID}}}__**, {{{name}}}, {{{content}}}, {{{is_public}}}, **__{{{user_ID*}}}__** (USER), {{{created_at}}}, {{{updated_at}}})

10. **NOTIFICATION** (**__{{{notification_ID}}}__**, {{{content}}}, {{{is_read}}}, {{{created_at}}})
    * content_type (multi-valued attribute, see table **CONTENT_TYPE**)

11. **CONTENT_TYPE** (**__{{{notification_ID*}}}__** (NOTIFICATION), **__{{{content_type}}}__**)

12. **AI_SUGGESTION** (**__{{{suggestion_ID}}}__**, {{{original_text}}}, {{{suggested_text}}}, {{{accepted}}}, {{{created_at}}}, {{{applied_at}}}, **__{{{story_ID*}}}__** (STORY))

13. **SUGGESTION_TYPE** (**__{{{suggestion_ID*}}}__** (AI_SUGGESTION), **__{{{suggestion_type}}}__**)

14. **LIKE** (**__{{{user_ID*}}}__** (USER), **__{{{story_ID*}}}__** (STORY), {{{created_at}}})

15. **COMMENT** (**__{{{comment_ID}}}__**, {{{content}}}, **__{{{user_ID*}}}__** (USER), **__{{{story_ID*}}}__** (STORY), {{{created_at}}}, {{{updated_at}}})

16. **COLLABORATION** (**__{{{user_ID*}}}__** (USER), **__{{{story_ID*}}}__** (STORY), {{{created_at}}})
    * role (multi-valued attribute, see table **ROLE**)
    * permission_level (multi-valued attribute, see table **PERMISSION_LEVEL**)

17. **ROLE** (**__{{{user_ID*}}}__** (COLLABORATION), **__{{{story_ID*}}}__** (COLLABORATION), **__{{{role}}}__**)

18. **PERMISSION_LEVEL** (**__{{{user_ID*}}}__** (COLLABORATION), **__{{{story_ID*}}}__** (COLLABORATION), **__{{{permission_level}}}__**)

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

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

21. **NOTIFY** (**__{{{user_ID*}}}__** (USER), **__{{{story_ID*}}}__** (STORY), **__{{{notification_ID*}}}__** (NOTIFICATION))

22. **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.3.png)]]