wiki:P1

ERModel Current version

Diagram

Data requirements

ENTITIES

BASE_ENTITY
Abstract superclass that provides audit fields for all entities in the system.
Note: This is a mapped superclass and does not create a separate table. All attributes are inherited by child entities.
Attributes:

  • created_at – timestamp, required. Automatically set when entity is created.
  • updated_at – timestamp, optional. Automatically updated when entity is modified.


Child entities: All entities in the system inherit from BASE_ENTITY: USER, STORY, CHAPTER, GENRE, READING_LIST, NOTIFICATION, AI_SUGGESTION, LIKE, COMMENT.

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

  • user_id – numeric identifier (primary key).


Inherited attributes from BASE_ENTITY:

  • created_at – timestamp, required.
  • updated_at – timestamp, optional.


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 (including BASE_ENTITY attributes).

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

  • user_id – inherited primary key.


Inheritance: Inherits all attributes from USER (including BASE_ENTITY attributes).

WRITER
Represents writers. Inherited from USER.
Candidate keys:

  • user_id – inherited primary key.


Inheritance: Inherits all attributes from USER (including BASE_ENTITY attributes).

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

  • story_id – surrogate numeric identifier (primary key).


Inherited attributes from BASE_ENTITY:

  • created_at – timestamp, required.
  • updated_at – timestamp, optional.


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.


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

  • chapter_id – surrogate numeric identifier (primary key).


Inherited attributes from BASE_ENTITY:

  • created_at – timestamp, required.
  • updated_at – timestamp, optional.


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.


GENRE
Represents a genre category for stories. Inherits from BASE_ENTITY.
Candidate keys:

  • genre_id – surrogate numeric identifier (primary key).


Inherited attributes from BASE_ENTITY:

  • created_at – timestamp, required.
  • updated_at – timestamp, optional.


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. Inherits from BASE_ENTITY.
Candidate keys:

  • list_id – surrogate numeric identifier (primary key).


Inherited attributes from BASE_ENTITY:

  • created_at – timestamp, required.
  • updated_at – timestamp, optional.


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. Inherits from BASE_ENTITY.
Candidate keys:

  • notification_id – surrogate numeric identifier (primary key).


Inherited attributes from BASE_ENTITY:

  • created_at – timestamp, required.
  • updated_at – timestamp, optional.


Attributes:

  • notification_id – numeric, required. Auto-generated primary key.
  • content – text, required. Content of the notification.
  • content_type – text, required. Type of notification content.


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

  • suggestion_id – surrogate numeric identifier (primary key).


Inherited attributes from BASE_ENTITY:

  • created_at – timestamp, required.
  • updated_at – timestamp, optional.


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.


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

  • Composite key: (user_id, story_id).


Inherited attributes from BASE_ENTITY:

  • created_at – timestamp, required.
  • updated_at – timestamp, optional.


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

  • comment_id – surrogate numeric identifier (primary key).


Inherited attributes from BASE_ENTITY:

  • created_at – timestamp, required.
  • updated_at – timestamp, optional.


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.

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.
  • permission_level – 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 users liking stories.
Cardinality: M:N (Many users can like many stories)
Keys: Composite key: (user_id, story_id) in LIKE.

is_commented (USER – COMMENT – STORY)
Represents users commenting on stories.
Cardinality: M:N (Many users can comment on many stories)
Keys: Foreign key: user_id in COMMENT references USER. Foreign key: story_id in COMMENT references STORY.

notify (USER – NOTIFICATION)
Represents users receiving notifications.
Cardinality: N:1 (Many notifications can be sent to one user)
Keys: Foreign key: user_id in NOTIFICATION references USER.

need_approval (AI_SUGGESTION – STORY)
Represents AI suggestions needing approval for stories.
Cardinality: N:1 (Many AI suggestions can be made for one story)
Keys: Foreign key: story_id in AI_SUGGESTION references STORY.

Last modified 24 hours ago Last modified on 01/16/26 20:26:52

Attachments (1)

Download all attachments as: .zip

Note: See TracWiki for help on using the wiki.