4.2 Entity Relationship Diagram
The ER diagram details the database structure we are planning to implement. If time permits, the ER diagram may be expanded and updated. Else, the ER diagram may be simplified and updated.
We shall be implementing our database with SQL, probably Heroku SQLite3.
Description: The user table stores the details of the users where user ID, username and email must be unique.
PRIMARY KEY: User ID. This is generated in our backend to uniquely identify a user.
Description: The Organization is the one that will be interacting with the folders and file manager.
PRIMARY KEY: Organization ID + User ID
Extra note: An organization can have multiple parents and multiple children to allow a more flexible way in setting permission controls.
However, a cycle whereby an organization may become a parent or grandparent or ... ancestor of itself. The solution for this use case will be handled by the back-end to prevent this.
Description: A session is a timed event that an organization can create from a single file.
PRIMARY KEY: Session ID
Extra Note: The session password can act as a first line of authorization for the audience like in the use case of a lecture session or a project meeting.
Description: To group the files
PRIMARY KEY: Folder ID + Organization ID
Extra Note: The folder main role is to store permission data for our software engineers to identify permission. Similar to folders in Windows, the folder are unique individuals who can only have at most 1 parent folder.
Similar to the organization, it is possible for a folder to be an ancestor of itself. The solution will be handled by the back-end of the software to prevent this.
Description: To handle files that the organizations (users) have created.
PRIMARY KEY: Organization ID + File ID
Extra Note: The file manager main role is to store permission data for our software engineers to identify who has the permission to access the file.
Description: Store images
PRIMARY KEY: Image ID
Description: To store text by paragraphs.
PRIMARY KEY: File ID + Text ID
Extra Note: The implementation will not be discussed in the ER diagram but the paragraphs order will be stored in another entity and each paragraph will be tied to some annotations.
Description: To store the order of the paragraph texts
PRIMARY KEY: File ID + Order Number + Paragraph Number
Extra Note: The paragraph order is useful to act as a pointer to keep the annotations in-line.
Description: To categorize the annotations.
PRIMARY KEY: Annotation ID
Extra Note: The annotations will be pinned to the paragraph to ensure it will not be messed up when the user sorts its notes.
Description: To store the annotation files
PRIMARY KEY: Annotation Number