Database Design – How to Design a Notifications Table?

database-design

I'm trying to create a notification system for my social network. But I'm stuck on the database design. so when a user comments on another user I want to display "X commented on your post." or when someone follows another I want to display a notification.

This is my table so far:

CREATE TABLE IF NOT EXISTS `notifications` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `notification_id` int(11) NOT NULL,
  `text` text NOT NULL,
  `read` int(11) NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

The part where I'm confused at is, Should I insert a record when someone follows another person? Like… Right after when someone clicks follow button? If so, What should I insert to the row?

Best Answer

Design your database around the data. What I mean by this is:

  • You have users
  • You have posts
  • You have comments
  • You have users you are following
  • You have notifications

It looks like you are trying to design your database around one use case rather than better organizing your data.

Try something like:

CREATE TABLE Users(
   id int not null auto_increment,
   name string not null
)

CREATE TABLE Posts(
   id int not null auto_increment,
   userID int foreign key Users(id),
   text string,
   whenPosted datetime default getDate()
)

CREATE TABLE Comments(
   id int not null auto_increment,
   userID int foreign key Users(id),
   postID int foreign key Posts(id),
   text string
)

CREATE TABLE Followers(
   userID int foreign key Users(id),
   followedUserID int foreign key Users(id)
)


CREATE TABLE Notifications(
   id int not null auto_increment,
   generated date default getDate(),
   text string //and other meta-data
   isRead bit not null default 0
)

so when a user comments on another user I want to display "X commented on your post."

When you generate a comment you can immediately insert a notification into the Notification table (this is straightforward).

Should I insert a record when someone follows another person? Like... Right after when someone clicks follow button? If so, What should I insert to the row?

Now, since you have a Followers table you can add them to that table on clicking that button.

You can then do either:

  1. Check when you submit a post in the future add a new notification (and ignore all previous posts)
  2. Immediately create a notification for all posts after a given time period

I recommend the first option, to me "follow" implies "follow all new content."

Related Topic