Is this Social Media Platform PostgreSQL Database design right?

Question

Here is a design of the persistence of a simple Social Media Platform. Currently, there are these tables:

Users: Main table of the database that contains the information of the users registered in our application. The data that will be stored in this table will be the name
- Name: users
- Fields: id, name, username, password, email, bio, followers, following, picture.
- Primary key: id
Posts: Database table with all the posts from all the users. Each post will contain the title, description, and the main content of the post.
- Name: posts
- Fields: id, title, picture, description, content, created_at, likes, user_id.
- Primary key: id
- Foreign key: user_id to table users
Post Liked by Users: A table that defines the many to many relationship between multiple posts liked and the users that liked them.
- Name: posts_liked_users
- Fields: post_id, user_id
- Foreign key: post_id to table posts
- Foreign key: user_id to table users
Follows. Table to be able to create a "following" relationship between users.
- Name: follows
- Fields: following_user_id, followed_user_id
- Foreign key: following_user_id to table users
- Foreign key: followed_user_id to table users

Here are the commands to create the tables

CREATE TABLE users(
    id SERIAL PRIMARY KEY,
    name VARCHAR (50) NOT NULL,
    username VARCHAR (50) UNIQUE NOT NULL,
    password VARCHAR (255) NOT NULL,
    email VARCHAR (255) NOT NULL,
    bio VARCHAR (255) NOT NULL,
    followers INTEGER NOT NULL,
    following INTEGER NOT NULL,
    picture VARCHAR (255) NOT NULL
  )

CREATE TABLE posts(
    id SERIAL PRIMARY KEY,
    title VARCHAR (255) NOT NULL,
    picture VARCHAR (255) NOT NULL,
    description VARCHAR (255) NOT NULL,
    content TEXT NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    likes INTEGER NOT NULL,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE
  )

CREATE TABLE posts_liked_users(
    post_id INTEGER NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE
  )


CREATE TABLE follows(
    following_user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    followed_user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE
  )

And here is the diagram:

Are the diagram and the overall design right or is there something missing?

Ask questions of you database and see if the database answers. In other words, can you create SELECT queries to answer your questions? If you can answer all the questions you or your users can think of, your database is correct. — Gilbert Le Blanc, Jun 13 '20 at 13:26
Thank you @GilbertLeBlanc that is a great advice. What about the diagram? do you think the diagram represents the right way the tables relationships? — reymon359, Jun 13 '20 at 15:16
Again, the SELECT statements you create will show you if the database can answer queries without **unnecessary** joins. Pretend you have a GUI. See if you can insert or update the database information you create with a pencil and paper. — Gilbert Le Blanc, Jun 13 '20 at 19:31
Ok, @GilbertLeBlanc. I will do as you suggested. Thank you again! — reymon359, Jun 14 '20 at 08:51

score 2 · Accepted Answer · answered Jul 03 '20 at 14:48

As an answer to anyone having a similar problem like the one from the question I refactored the design based on some suggestions and research:

I updated the VARCHAR fields to be TEXT which is the general guidance.
Because of normalization I removed the followers and following from the users and likes from the post in order to reduce data redundancy and improve data integrity.
I added a created_at field on the follows and posts_liked_users to keep the time when a user followed another or liked a post.

CREATE TABLE users(
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  username TEXT UNIQUE NOT NULL,
  password TEXT NOT NULL,
  email TEXT NOT NULL,
  bio TEXT NOT NULL,
  picture TEXT NOT NULL
)

CREATE TABLE posts(
  id SERIAL PRIMARY KEY,
  title TEXT NOT NULL,
  picture TEXT NOT NULL,
  description TEXT NOT NULL,
  content TEXT NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE
)

CREATE TABLE posts_liked_users(
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  post_id INTEGER NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE
)

CREATE TABLE follows(
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  following_user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  followed_user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE
)

references:

Is this Social Media Platform PostgreSQL Database design right?

1 Answers1