Designing a social media notification system, would a single table with JSON column be the correct approach for data?

Question

I am trying to create a notification system similar to Instagram, for the lack of better comparasion.

The Goal:

Getting user notifications in a unify way, for easy unparsing and displaying to user.

The Problems:

1. Mixed Data - Different body types needing unification - "New Follower" notification requires user details. "FooBatz made a new post" requires different parameters

2. Changing Data - depending on when the user views it, New Follower notification will need to display the current profile picture, not the one that was when he followed.

3. Storage - Many duplicated notifications, "FooBatz" made a new post will trigger notification for all of his followers, he's very popular.

Solutions:

1. For mixed data - Using SQL json to store the data, keeping it all in one table.

2. For Changing data - Passing the user only the raw fields and having him send another request for full info, such as profile picture.

3. For storage, separate tables for data and status, so the status row links to the data, while only storing the read status.

4. For the code handling counting on typescript smart typing to recognize:

interface NewFollowerNotification {
 type: 'newFollower'
 data: {userId: string, profilePicture: string}
}
interface NewFollowerPostNotification {
 type: 'NewFollowerPost'
 data: {userId: string, postId: string}
}
type Notification = NewFollowerNotification | NewFollowerPostNotification
const test: Notification = {
 type: 'NewFollowerPost'
 data: {
 userId: 'foo',
 profilePicture: 'batz` // <- will show error
 }
}

Concerns

1. Swarming requests as user checks his notifications, as he will fire many requests for extra data - getting user informations, pictures and more that will greatly slow the website loading and hurt the user experience.

2. With SQL JSON it can get chaotic and will be hard to track and normalize.

3. With SQL JSON I cannot effectively do joins to return all the relevant data in a single request.

4. Relying on as for typescript casting as the database module wouldn't actually do checks for typing.

Would that system scale well? Or should I consider a different approach

• Hans-Martin Mosner: These are a number of questions, and what will or won't work depends a lot on the context and environment. One pretty established approach is: * Make it work * Make it right * Make it fast --- I'll add: * Focus on one problem at a time (this is especially important on Stack Exchange)
• Basilevs: @Hans-MartinMosner do have a reference for the quote? I'm considering questioning it.
• Hans-Martin Mosner: @Basilevs wiki.c2.com/?MakeItWorkMakeItRightMakeItFast - as with all witty sayings, to be taken with a grain of salt.
• Greg Burghardt: And sometimes I also pair that grain of salt with a wedge of lime, and a shot of tequila. Remember that every tool or technique has its use cases, so it is never a universal "yes" or "no" (sorry, referring to my Software Engineering Chat with @Basilevs from a few minutes ago).
• Basilevs: @GregBurghardt a link to the chat message

Answer 1

For mixed data - Using SQL json to store the data, keeping it all in one table.

Have you considered NoSQL?

Many NoSQL databases, including but not limited to Elasticsearch and MongoDB, were designed from the start to work with JSON documents. As opposed to an afterthought with popular SQL engines, where support for JSON, XML, or any other type of non-structured data, was added much later, providing rather poor support for it.

Swarming requests as user checks his notifications, as he will fire many requests for extra data - getting user informations, pictures and more that will greatly slow the website loading and hurt the user experience.

Greatly? Why?

What's the percentage of your users who don't support HTTP/2, making it impossible to use multiplexing?

Would that system scale well? Or should I consider a different approach

You get ahead of yourself. You are talking about Instagram and thinking about scalability, and yet, you haven't released any MVP so far.

You have an idea and a willingness to work on it. That's great. Play with different technologies. Build a prototype. Throw it away. Build another one. Rebuild it. Several times.

At some point, you'll have a clear idea what works well and what doesn't. More importantly, you'll have a working MVP that you can play with.

Maybe it would be a complete failure. That's perfectly normal and nothing to be worried about: you spent great time, and learned something.

Maybe it would be usable. As in—the actual people come and use your piece of software. Then you'll start addressing the problems that would arise. Maybe it would be scalability. Fine. You'll have at this point a very concrete case with metrics to back it up—you would then ask your colleagues or people from Stack Exchange to help you solving it. Or maybe performances won't be an issue, but everybody would complain about user experience—then you'll work on UX instead. Who knows.

"Well, what if, right now, I make a wrong choice, and it will haunt me when I would need to scale the application up?"—you may ask.

If you design your application properly, "wrong choices" such as a sub-optimal database system won't be your problem. I designed quite a few APIs which, at the early stage, were storing data in a plain text file on disk. This is absolutely terrible in terms of scalability. But as the application grew, I always had an opportunity to swap this early storage mechanism by something more mature—given that at a later stage, I also had more visibility on the different requirements and limitations, and could make better choices.

• eliezra236: Thank you for this very detailed response! I think you're right, for now I will use this method, and we'll see down the road if it will haunt me or not. I guess that if it would grow enough that would be a "good issue" to deal with as it means the application is successful to begin with.

Answer 2

Designing a social media notification system, would a single table with JSON column be the correct approach for data?

The answer is always no. JSON data format is designed to transport data. There is the option to derail the database in a way to make JSON seem a proper approach though database standard format to store data is plain, clean and performant.

The database model to achieve results akin to JSON format is entity-attribute-value (EAV) model.