Udiddit, a social news aggregation, web content rating, and discussion website, is currently using a risky and unreliable Postgres database schema to store the forum posts, discussions, and votes made by their users about different topics. I create a new schema, fix exsiting issues, and migrate the data from the databases.
- Udiddit, a social news aggregator.docx - Contains the DDL of old schema, design guidlines of new schema DDL, and DML data migration explanations.
- Project #2 - The sql code used to improve the DDL of old schema and DML to migrate data.
I wrote a new Schema to accomodate the following:
- Guideline #1: Here is a list of features and specifications that Udiddit needs in order to support its website and administrative interface:
- A. Allow new users to register:
- i. Each username has to be unique
- ii. Usernames can be composed of at most 25 characters
- iii. Usernames can’t be empty
- iv. We won’t worry about user passwords for this project
- B. Allow registered users to create new topics:
- i. Topic names have to be unique.
- ii. The topic’s name is at most 30 characters
- iii. The topic’s name can’t be empty
- iv. Topics can have an optional description of at most 500 characters.
- C. Allow registered users to create new posts on existing topics:
- i. Posts have a required title of at most 100 characters
- ii. The title of a post can’t be empty.
- iii. Posts should contain either a URL or a text content, but not both.
- iv. If a topic gets deleted, all the posts associated with it should be automatically deleted too.
- v. If the user who created the post gets deleted, then the post will remain, but it will become dissociated from that user.
- D. Allow registered users to comment on existing posts:
- i. A comment’s text content can’t be empty.
- ii. Contrary to the current linear comments, the new structure should allow comment threads at arbitrary levels.
- iii. If a post gets deleted, all comments associated with it should be automatically deleted too.
- iv. If the user who created the comment gets deleted, then the comment will remain, but it will become dissociated from that user.
- v. If a comment gets deleted, then all its descendants in the thread structure should be automatically deleted too.
- E. Make sure that a given user can only vote once on a given post:
- i. Hint: you can store the (up/down) value of the vote as the values 1 and -1 respectively.
- ii. If the user who cast a vote gets deleted, then all their votes will remain, but will become dissociated from the user.
- iii. If a post gets deleted, then all the votes for that post should be automatically deleted too.
- Guideline #2: Here is a list of queries that Udiddit needs in order to support its website and administrative interface. Note that I don’t need to produce the DQL for those queries: they are only provided to guide the design of my new database schema.
- A. List all users who haven’t logged in in the last year.
- B. List all users who haven’t created any post.
- C. Find a user by their username.
- D. List all topics that don’t have any posts.
- E. Find a topic by its name.
- F. List the latest 20 posts for a given topic.
- G. List the latest 20 posts made by a given user.
- H. Find all posts that link to a specific URL, for moderation purposes.
- I. List all the top-level comments (those that don’t have a parent comment) for a given post.
- J. List all the direct children of a parent comment.
- K. List the latest 20 comments made by a given user.
- L. Compute the score of a post, defined as the difference between the number of upvotes and the number of downvotes.
- Guideline #3: I’ll need to use normalization, various constraints, as well as indexes in my new database schema. I should use named constraints and indexes to make my schema cleaner.
- Guideline #4: My new database schema will be composed of five (5) tables that should have an auto-incrementing id as their primary key.