ERModel Current version

Diagram

Data requirements

ENTITIES

USER
Represents each individual user at the platform.
Candidate keys:

• user_id – numeric identifier (primary key).

Attributes:

• user_id – numeric, required. Primary key.
• username – text, required, unique.
• email – text, required, unique.
• name – text, required.
• surname – text, required.
• password – hash value, required.

---

ADMIN
Represents the admin user (full access of the system). Inherited from USER.
Candidate keys:

• user_id – inherited primary key.

Inheritance: Inherits all attributes from USER.

---

REGULAR_USER
Represents regular users. Inherited from USER.
Candidate keys:

• user_id – inherited primary key.

Inheritance: Inherits all attributes from USER

---

WRITER
Represents writers. Inherited from USER.
Candidate keys:

• user_id – inherited primary key.

Inheritance: Inherits all attributes from USER

---

STORY
Represents every story that is written in the platform.
Candidate keys:

• story_id – surrogate numeric identifier (primary key).

Attributes:

• story_id – numeric, required. Auto-generated primary key.
• mature_content – boolean, required. Indicates if story contains mature content.
• short_description – text, required. Brief description of the story.
• content – text, required. Full content of the story.
• status – multi-valued text, required. Publication status (draft, published, archived). A story can have multiple status values over time.
• image – text, optional. URL or path to story cover image.

---

CHAPTER
Represents a chapter, which is part of a story.
Candidate keys:

• chapter_id – surrogate numeric identifier (primary key).

Attributes:

• chapter_id – numeric, required. Auto-generated primary key.
• chapter_number – numeric, required. Order number of the chapter within the story.
• chapter_name – text, required. Name of the chapter.
• title – text, required. Title of the chapter.
• content – text, required. Content of the chapter.
• word_count – numeric, optional. Number of words in the chapter.
• rating – numeric, optional. Rating of the chapter.
• published_at – timestamp, required. Publication date and time.
• view_count – numeric, optional. Number of views.

---

GENRE
Represents a genre category for stories.
Candidate keys:

• genre_id – surrogate numeric identifier (primary key).

Attributes:

• genre_id – numeric, required. Auto-generated primary key.
• name – text, required, unique. Name of the genre.

---

READING_LIST
Represents a reading list created by users.
Candidate keys:

• list_id – surrogate numeric identifier (primary key).

Attributes:

• list_id – numeric, required. Auto-generated primary key.
• name – text, required. Name of the reading list.
• content – text, optional. Description of the reading list.
• is_public – boolean, required. Visibility of the reading list.

---

NOTIFICATION
Represents notifications sent to users.
Candidate keys:

• notification_id – surrogate numeric identifier (primary key).

Attributes:

• notification_id – numeric, required. Auto-generated primary key.
• content – text, required. Content of the notification.
• content_type – multi-valued text, required. Type of notification content. A notification can belong to multiple content types.
• is_read – boolean, required. Indicates whether the notification has been read. Default: false.

---

AI_SUGGESTION
Represents AI-generated suggestions for story improvements.
Candidate keys:

• suggestion_id – surrogate numeric identifier (primary key).

Attributes:

• suggestion_id – numeric, required. Auto-generated primary key.
• original_text – text, required. Original text from the story.
• suggested_text – text, required. AI-suggested improvement.
• suggestion_type – multi-valued text, required. Type of suggestion (grammar, style, etc.). A suggestion can belong to multiple types.
• accepted – boolean, required. Whether the suggestion was accepted.

---

LIKE
Represents likes given by users to stories.
Candidate keys:

• Composite key: (user_id, story_id).

---

COMMENT
Represents comments made by users on stories.
Candidate keys:

• comment_id – surrogate numeric identifier (primary key).

Attributes:

• comment_id – numeric, required. Auto-generated primary key.
• content – text, required. Content of the comment.

---

RELATIONS

has_list (USER – READING_LIST)
Represents users having reading lists.
Cardinality: 1:N (One user can have many reading lists, each reading list belongs to one user)
Keys: Foreign key: user_id in READING_LIST references USER.

---

reading_list_items (READING_LIST – STORY)
Represents stories added to reading lists.
Cardinality: Cardinality: M:N (Many reading lists can contain many stories))
Keys: Keys: Composite key: (list_id, story_id).
Attributes:

• added_at – timestamp, required. Date and time when the story was added to the reading list.

---

collaboration (USER – STORY)
Represents users collaborating on stories.
Cardinality: M:N (Many users can collaborate on many stories)
Keys: Composite key: (user_id, story_id).
Attributes:

• role – multi-valued text, required. Role of the collaborator.
• permission_level – multi-valued numeric, optional. Level of permission for collaboration.

---

created_story (WRITER – STORY)
Represents writers creating stories.
Cardinality: 1:N (One writer can create many stories, each story is created by one writer)
Keys: Foreign key: user_id in STORY references WRITER.

---

has_genre (STORY – GENRE)
Represents stories having genres.
Cardinality: M:N (Many stories can have many genres)
Keys: Composite key: (story_id, genre_id).

---

part_of (CHAPTER – STORY)
Represents chapters being part of stories.
Cardinality: N:1 (Many chapters are part of one story)
Keys: Foreign key: story_id in CHAPTER references STORY.

---

is_liked (USER – LIKE – STORY)
Represents likes given by users to stories.
Cardinality: User 1:N LIKE, Story 1:N LIKE (One user can like many stories, one story can have many likes)
Keys: Composite key: (user_id, story_id) in LIKE Foreign key: user_id in LIKE references USER. Foreign key: story_id in LIKE references STORY.

---

is_commented (USER – COMMENT – STORY)
Represents comments made by users on stories.
Cardinality: User 1:N COMMENT, Story 1:N COMMENT (One user can make many comments, one story can have many comments)
Keys: Foreign key: user_id in COMMENT references USER. Foreign key: story_id in COMMENT references STORY.

---

notify (USER – STORY – NOTIFICATION)
Represents notifications sent to users about stories.
Cardinality: M:N:P (Many users can receive many notifications about many stories)
Keys: Composite key: (user_id,story_id,notification_id) Foreign key: user_id references USER Foreign key: story_id references STORY. Foreign key: notification_id references NOTIFICATION.

---

need_approval (AI_SUGGESTION – STORY – CHAPTER)
Represents AI suggestions needing approval for specific chapters within stories.
Cardinality: M:N:P (Many AI suggestions can be made for many stories across many chapters)
Keys: Composite key: (suggestion_id,story_id,chapter_id) Foreign key: suggestion_id references AI_SUGGESTION. Foreign key: story_id references STORY. Foreign key: chapter_id references CHAPTER.

---

suggested (STORY – AI_SUGGESTION)
Represents stories having AI-generated suggestions.
Cardinality: 1:N (One story can have many AI suggestions)
Keys: Foreign key: story_id in AI_SUGGESTION references STORY.