Database Design & Data Processing

Database Design

SQL Server Express was the chosen database management system for Wedium. The database outlined below is live and executing on a server in the Netherlands.

Database Schema

The Wedium database contains the following tables:

• User: Maintains all information related to users.
  • UserID (int) primary key
  • FirstName (nvarchar)
  • LastName (nvarchar)
  • Email (nvarchar) unique
• Settings: Maintains changeable settings utilised by the Wedium API (allows for changeable behaviour without requiring rebuilding the API)
  • SettingsID (int) primary key
  • Key (nvarchar) unique
  • Value (nvarchar)
• Post: Maintains posts made by users
  • PostID (int) primary key
  • Date (datetime) date at time of creation
  • Title (nvarchar)
  • Description (nvarchar)
  • UserID (int) foreign key on User
  • WikiArticleID (int) foreign key on WikiArticle
  • PostTypeID (int) foreign key on PostType
• WikiArticle: Maintains relevant Wikipedia article metadata and information required to avoid copyright issues.
  • WikiArticleID (int) primary key
  • URL (nvarchar) required to avoid copyright
  • ArticleDate (nvarchar)
  • ArticleBody (nvarchar) contains article's summary
  • ArticleTitle (nvarchar)
  • ArticleImageURL (nvarchar) nullable contains image url of notable image in url
• Comment: Maintains comments which have been made on posts.
  • CommendID (int) primary key
  • Date (datetime) date at time of creation
  • Body (nvarchar)
  • PostID (int) foreign key on Post
  • UserID (int) foreign key on User
  • ParentCommentID (int) foreign key on Comment nullable allows for nesting of comments (null if top-level comment)
  • CommentTypeID (int) foreign key on CommentType
• Favourite: Maintains all favourited posts by users.
  • FavouriteID (int) primary key
  • Date (datetime) date at time of creation
  • UserID (int) foreign key on User
  • PostID (int) foreign key on Post
unique on UserID, PostID
• PostLike: Maintains all posts liked by users.
  • PostLikeID (int) primary key
  • Date (datetime) date at time of creation
  • UserID (int) foreign key on User
  • PostID (int) foreign key on Post
unique on UserID, PostID
• CommentLike: Maintains all comments liked by users.
  • CommentLikeID (int) primary key
  • Date (datetime) date at time of creation
  • UserID (int) foreign key on User
  • CommentID(int) foreign key on Comment
unique on UserID, CommentID
• PostType: Maintains the allowable types of posts, retrieved from the Wikipedia Article (arts, biography, geography...).
  • PostTypeID (int) primary key
  • PostTypeValue (nvarchar) unique
• CommentType: Maintains the allowable types of comments (statements, questions).
  • CommentTypeID (int) primary key
  • CommentTypeValue (nvarchar) unique

Database Roles

To provide security to the data held within the database, the following roles exist:

• Wedium Admin: Has elevated rights to the database, allowing for more advanced manipulation of data and structures.
• Wedium User: Has basic rights required to allow users to create/view accounts, posts and comments. This role is used by Wedium's API and therefore provides a safeguard from attacks involving the API.

Interactive Tool

An interactive visual representation of the database can be seen here. Due to dbdiagram limitations, inconsistencies may exist between database and diagram. Please refer to this wiki page for correct information.

Data Processing

The data will managed through the use of Entity Framework Core. The use of an Object-Relational-Mapper was chosen as it allows for complex SQL-like queries to be created for data retrieval, while also providing the advantages which object oriented programming languages, such as C#, have. Furthermore, after setup, using Entity Framework would result in faster API development as the team is more familiar with using ORM than writing SQL stored procedures.

ORM Models

The ORM Model classes were reverse engineered through Entity Framework's Scaffold-DbContext command. Information regarding how to do this can be seen here.