Changes between Initial Version and Version 1 of P1

Timestamp:

01/16/26 20:07:47 (27 hours ago)

Author:

211099

Comment:

Initial ER diagram implementation

P1

@@ +1 @@
+== ERModel Current version ==
+== Diagram ==
+[[Image()]]
+
+== [[Image([[Image()]])]]
+== Data requirements ==
+=== ENTITIES ===
+
+{{{BASE_ENTITY}}}
+[[BR]]
+Abstract superclass that provides audit fields for all entities in the system.
+[[BR]]
+'''[[span(style=color: #9A0000, Note:)]]'''
+This is a mapped superclass and does not create a separate table. All attributes are inherited by child entities.
+[[BR]]
+'''[[span(style=color: #9A0000, Attributes:)]]'''
+* {{{created_at}}} – timestamp, required. Automatically set when entity is created.
+* {{{updated_at}}} – timestamp, optional. Automatically updated when entity is modified.
+[[BR]]
+'''[[span(style=color: #9A0000, Child entities:)]]'''
+All entities in the system inherit from BASE_ENTITY: USER, STORY, CHAPTER, GENRE, READING_LIST, NOTIFICATION, AI_SUGGESTION, LIKE, COMMENT.
+[[BR]]
+----
+
+{{{USER}}}
+[[BR]]
+Represents each individual user at the platform. 
+[[BR]]
+'''[[span(style=color: #9A0000, Candidate keys:)]]'''
+* {{{user_id}}} – numeric identifier (primary key).
+[[BR]]
+'''[[span(style=color: #9A0000, Inherited attributes from BASE_ENTITY:)]]'''
+* {{{created_at}}} – timestamp, required.
+* {{{updated_at}}} – timestamp, optional.
+[[BR]]
+'''[[span(style=color: #9A0000, 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.
+* {{{role}}} – text, required. User role in the system.
+* {{{permission_level}}} – numeric, optional. Permission level for the user.
+[[BR]]
+----
+
+{{{ADMIN}}}
+[[BR]]
+Represents the admin user (full access of the system). Inherited from USER.
+[[BR]]
+'''[[span(style=color: #9A0000, Candidate keys:)]]'''
+* {{{user_id}}} – inherited primary key.
+[[BR]]
+'''[[span(style=color: #9A0000, Inheritance:)]]'''
+Inherits all attributes from USER (including BASE_ENTITY attributes).
+[[BR]]
+----
+
+{{{REGULAR_USER}}}
+[[BR]]
+Represents regular users. Inherited from USER.
+[[BR]]
+'''[[span(style=color: #9A0000, Candidate keys:)]]'''
+* {{{user_id}}} – inherited primary key.
+[[BR]]
+'''[[span(style=color: #9A0000, Inheritance:)]]'''
+Inherits all attributes from USER (including BASE_ENTITY attributes).
+[[BR]]
+----
+
+{{{WRITER}}}
+[[BR]]
+Represents writers. Inherited from USER.
+[[BR]]
+'''[[span(style=color: #9A0000, Candidate keys:)]]'''
+* {{{user_id}}} – inherited primary key.
+[[BR]]
+'''[[span(style=color: #9A0000, Inheritance:)]]'''
+Inherits all attributes from USER (including BASE_ENTITY attributes).
+[[BR]]
+----
+
+{{{STORY}}}
+[[BR]]
+Represents every story that is written in the platform. '''Inherits from BASE_ENTITY.'''
+[[BR]]
+'''[[span(style=color: #9A0000, Candidate keys:)]]'''
+* {{{story_id}}} – surrogate numeric identifier (primary key).
+[[BR]]
+'''[[span(style=color: #9A0000, Inherited attributes from BASE_ENTITY:)]]'''
+* {{{created_at}}} – timestamp, required.
+* {{{updated_at}}} – timestamp, optional.
+[[BR]]
+'''[[span(style=color: #9A0000, 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}}} – text, required. Publication status (draft, published, archived).
+* {{{image}}} – text, optional. URL or path to story cover image.
+[[BR]]
+----
+
+{{{CHAPTER}}}
+[[BR]]
+Represents a chapter, which is part of a story. '''Inherits from BASE_ENTITY.'''
+[[BR]]
+'''[[span(style=color: #9A0000, Candidate keys:)]]'''
+* {{{chapter_id}}} – surrogate numeric identifier (primary key).
+[[BR]]
+'''[[span(style=color: #9A0000, Inherited attributes from BASE_ENTITY:)]]'''
+* {{{created_at}}} – timestamp, required.
+* {{{updated_at}}} – timestamp, optional.
+[[BR]]
+'''[[span(style=color: #9A0000, Attributes:)]]'''
+* {{{chapter_id}}} – numeric, required. Auto-generated primary key.
+* {{{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.
+[[BR]]
+----
+
+{{{GENRE}}}
+[[BR]]
+Represents a genre category for stories. '''Inherits from BASE_ENTITY.'''
+[[BR]]
+'''[[span(style=color: #9A0000, Candidate keys:)]]'''
+* {{{genre_id}}} – surrogate numeric identifier (primary key).
+[[BR]]
+'''[[span(style=color: #9A0000, Inherited attributes from BASE_ENTITY:)]]'''
+* {{{created_at}}} – timestamp, required.
+* {{{updated_at}}} – timestamp, optional.
+[[BR]]
+'''[[span(style=color: #9A0000, Attributes:)]]'''
+* {{{genre_id}}} – numeric, required. Auto-generated primary key.
+* {{{name}}} – text, required, unique. Name of the genre.
+[[BR]]
+----
+
+{{{READING_LIST}}}
+[[BR]]
+Represents a reading list created by users. '''Inherits from BASE_ENTITY.'''
+[[BR]]
+'''[[span(style=color: #9A0000, Candidate keys:)]]'''
+* {{{list_id}}} – surrogate numeric identifier (primary key).
+[[BR]]
+'''[[span(style=color: #9A0000, Inherited attributes from BASE_ENTITY:)]]'''
+* {{{created_at}}} – timestamp, required.
+* {{{updated_at}}} – timestamp, optional.
+[[BR]]
+'''[[span(style=color: #9A0000, 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.
+[[BR]]
+----
+
+{{{NOTIFICATION}}}
+[[BR]]
+Represents notifications sent to users. '''Inherits from BASE_ENTITY.'''
+[[BR]]
+'''[[span(style=color: #9A0000, Candidate keys:)]]'''
+* {{{notification_id}}} – surrogate numeric identifier (primary key).
+[[BR]]
+'''[[span(style=color: #9A0000, Inherited attributes from BASE_ENTITY:)]]'''
+* {{{created_at}}} – timestamp, required.
+* {{{updated_at}}} – timestamp, optional.
+[[BR]]
+'''[[span(style=color: #9A0000, Attributes:)]]'''
+* {{{notification_id}}} – numeric, required. Auto-generated primary key.
+* {{{content}}} – text, required. Content of the notification.
+* {{{content_type}}} – text, required. Type of notification content.
+[[BR]]
+----
+
+{{{AI_SUGGESTION}}}
+[[BR]]
+Represents AI-generated suggestions for story improvements. '''Inherits from BASE_ENTITY.'''
+[[BR]]
+'''[[span(style=color: #9A0000, Candidate keys:)]]'''
+* {{{suggestion_id}}} – surrogate numeric identifier (primary key).
+[[BR]]
+'''[[span(style=color: #9A0000, Inherited attributes from BASE_ENTITY:)]]'''
+* {{{created_at}}} – timestamp, required.
+* {{{updated_at}}} – timestamp, optional.
+[[BR]]
+'''[[span(style=color: #9A0000, 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.).
+* {{{accepted}}} – boolean, required. Whether the suggestion was accepted.
+[[BR]]
+----
+
+{{{LIKE}}}
+[[BR]]
+Represents likes given by users to stories. '''Inherits from BASE_ENTITY.'''
+[[BR]]
+'''[[span(style=color: #9A0000, Candidate keys:)]]'''
+* Composite key: ({{{user_id}}}, {{{story_id}}}).
+[[BR]]
+'''[[span(style=color: #9A0000, Inherited attributes from BASE_ENTITY:)]]'''
+* {{{created_at}}} – timestamp, required.
+* {{{updated_at}}} – timestamp, optional.
+[[BR]]
+----
+
+{{{COMMENT}}}
+[[BR]]
+Represents comments made by users on stories. '''Inherits from BASE_ENTITY.'''
+[[BR]]
+'''[[span(style=color: #9A0000, Candidate keys:)]]'''
+* {{{comment_id}}} – surrogate numeric identifier (primary key).
+[[BR]]
+'''[[span(style=color: #9A0000, Inherited attributes from BASE_ENTITY:)]]'''
+* {{{created_at}}} – timestamp, required.
+* {{{updated_at}}} – timestamp, optional.
+[[BR]]
+'''[[span(style=color: #9A0000, Attributes:)]]'''
+* {{{comment_id}}} – numeric, required. Auto-generated primary key.
+* {{{content}}} – text, required. Content of the comment.
+[[BR]]
+----
+
+=== RELATIONS ===
+
+{{{has_list (USER – READING_LIST)}}}
+[[BR]]
+Represents users having reading lists.
+[[BR]]
+'''[[span(style=color: #9A0000, Cardinality:)]]'''
+1:N (One user can have many reading lists, each reading list belongs to one user)
+[[BR]]
+'''[[span(style=color: #9A0000, Keys:)]]'''
+Foreign key: {{{user_id}}} in READING_LIST references USER.
+[[BR]]
+----
+
+{{{collaboration (USER – STORY)}}}
+[[BR]]
+Represents users collaborating on stories.
+[[BR]]
+'''[[span(style=color: #9A0000, Cardinality:)]]'''
+M:N (Many users can collaborate on many stories)
+[[BR]]
+'''[[span(style=color: #9A0000, Keys:)]]'''
+Composite key: ({{{user_id}}}, {{{story_id}}}).
+[[BR]]
+'''[[span(style=color: #9A0000, Attributes:)]]'''
+* {{{role}}} – text, required. Role of the collaborator.
+* {{{permission_level}}} – numeric, optional. Level of permission for collaboration.
+[[BR]]
+----
+
+{{{created_story (WRITER – STORY)}}}
+[[BR]]
+Represents writers creating stories.
+[[BR]]
+'''[[span(style=color: #9A0000, Cardinality:)]]'''
+1:N (One writer can create many stories, each story is created by one writer)
+[[BR]]
+'''[[span(style=color: #9A0000, Keys:)]]'''
+Foreign key: {{{user_id}}} in STORY references WRITER.
+[[BR]]
+----
+
+{{{has_genre (STORY – GENRE)}}}
+[[BR]]
+Represents stories having genres.
+[[BR]]
+'''[[span(style=color: #9A0000, Cardinality:)]]'''
+M:N (Many stories can have many genres)
+[[BR]]
+'''[[span(style=color: #9A0000, Keys:)]]'''
+Composite key: ({{{story_id}}}, {{{genre_id}}}).
+[[BR]]
+----
+
+{{{part_of (CHAPTER – STORY)}}}
+[[BR]]
+Represents chapters being part of stories.
+[[BR]]
+'''[[span(style=color: #9A0000, Cardinality:)]]'''
+N:1 (Many chapters are part of one story)
+[[BR]]
+'''[[span(style=color: #9A0000, Keys:)]]'''
+Foreign key: {{{story_id}}} in CHAPTER references STORY.
+[[BR]]
+----
+
+{{{is_liked (USER – LIKE – STORY)}}}
+[[BR]]
+Represents users liking stories.
+[[BR]]
+'''[[span(style=color: #9A0000, Cardinality:)]]'''
+M:N (Many users can like many stories)
+[[BR]]
+'''[[span(style=color: #9A0000, Keys:)]]'''
+Composite key: ({{{user_id}}}, {{{story_id}}}) in LIKE.
+[[BR]]
+----
+
+{{{is_commented (USER – COMMENT – STORY)}}}
+[[BR]]
+Represents users commenting on stories.
+[[BR]]
+'''[[span(style=color: #9A0000, Cardinality:)]]'''
+M:N (Many users can comment on many stories)
+[[BR]]
+'''[[span(style=color: #9A0000, Keys:)]]'''
+Foreign key: {{{user_id}}} in COMMENT references USER.
+Foreign key: {{{story_id}}} in COMMENT references STORY.
+[[BR]]
+----
+
+{{{notify (USER – NOTIFICATION)}}}
+[[BR]]
+Represents users receiving notifications.
+[[BR]]
+'''[[span(style=color: #9A0000, Cardinality:)]]'''
+N:1 (Many notifications can be sent to one user)
+[[BR]]
+'''[[span(style=color: #9A0000, Keys:)]]'''
+Foreign key: {{{user_id}}} in NOTIFICATION references USER.
+[[BR]]
+----
+
+{{{need_approval (AI_SUGGESTION – STORY)}}}
+[[BR]]
+Represents AI suggestions needing approval for stories.
+[[BR]]
+'''[[span(style=color: #9A0000, Cardinality:)]]'''
+N:1 (Many AI suggestions can be made for one story)
+[[BR]]
+'''[[span(style=color: #9A0000, Keys:)]]'''
+Foreign key: {{{story_id}}} in AI_SUGGESTION references STORY.
+[[BR]]
+----