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

DDL script

DML script for inserting data in the tables

DML script

Relational diagram made in DBeaver