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.

Attributes:

• assigned_at – timestamp, required. Date and time when admin privileges were assigned.

Inheritance: Inherits all attributes from USER.

---

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

• user_id – inherited primary key.

Attributes:

• joined_at – timestamp, required. Date and time when the user joined the platform.

Inheritance: Inherits all attributes from USER

---

WRITER
Represents writers. Inherited from USER.
Candidate keys:

• user_id – inherited primary key.

Attributes:

• bio – ext, optional. Short biography of the writer.

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.
• title – text, required. Title of the story.
• 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 – text, required. Publication status (draft, published, archived). A story has exactly one status at a 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.
• link – text, required. Link
• content_type – text, required. Type of notification content. Each notification has exactly one content type.
• 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 – text, required. Type of suggestion (grammar, style, etc.). Each suggestion has exactly one type.
• accepted – boolean, required. Whether the suggestion was accepted.

---

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

• Composite key: (user_id, story_id) - inherited from the identifying relationships.

Attributes:

• liked_at - timestamp, required. Date and time when the like was given.

---

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 - text, required. Role of the collaborator. Exactly one role per user per story.
• permission_level - numeric, optional. Level of permission. Exactly one level per user per story.

---

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.

---

gives_like (USER – LIKE)
Identifying relationship. Represents a user giving a like to a story.
Cardinality: 1:M (One user can give many likes, each like is given by exactly one user)
Keys: Foreign key: user_id in LIKE references USER (part of composite key of LIKE).

---

for_story (LIKE – STORY)
Identifying relationship. Represents the story a like refers to.
Cardinality: N:1 (One story can have many likes, each like refers to exactly one story)
Keys: Foreign key: story_id in LIKE references STORY (part of composite key of LIKE).

---

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.

---

receives (USER – NOTIFICATION)
Represents notifications received by users.
Cardinality: 1:N (One user can receive many notifications, each notification belongs to exactly one user)
Keys: Foreign key: user_id in NOTIFICATION references USER.

---

notify_about (NOTIFICATION – STORY)
Represents the story a notification refers to.
Cardinality: N:1 (One story can generate many notifications, each notification refers to exactly one story)
Keys: Foreign key: story_id in NOTIFICATION references STORY.

---

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.