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

DDL script

DML script for inserting data in the tables

DML script

Relational diagram made in DBeaver