Database – Should I use NoSQL (MongoDB) or SQL (postgresql) database for the application

databasedatabase-designmongodbnosqlrdbms

I am planning to build a live audience interaction webapp similar to Slido as practice/fun project.

User groups: admins and anonymous users

Admins:

  • login

  • create event (define event period and unique event code/name)

  • edit/delete user questions

  • highlight user questions (up to 3 questions per event)

Anonymous users:

  • join event by keying in event code/name

  • join event if event period matches current time

  • ask questions in the event page

  • see live updates of questions asked by other users

  • sort questions asked by time/most upvote

  • can upvote/downvote other questions

For such an application, will NoSQL or SQL db be a better choice?

Thanks in advance for your advices.

Updates!

Below are the data models for relational and non-relational scheme that i came up with. would love to have feedback on it:

Relational scheme:

Admins

  • id (serial primary key)

  • login

  • password

  • name

Events

  • id (serial primary key)

  • event_name

  • event_code

  • start_time

  • end_time

  • admin_id (foreign key reference admin.id)

Users

  • id (serial primary key)

  • name

  • email

Questions

  • id (serial primary key)

  • event_id (foreign key reference events.id)

  • post_time

  • user_id (foreign key reference users.id)

  • content

Question_upvote

  • id (serial primary key)

  • question_id (foreign key reference questions.id)

  • user_id (foreign key reference users.id)

Question_highlight (used by admins)

  • id (serial primary key)

  • question_id (foreign key reference questions.id)

Non-relational scheme:

Admins document

{
 uid: 
 login:
 password: 
 name:
 events: [event1, event2 ...]
}

Events document 

{
 uid:
eventName:
eventCode:
startTime:
endTime:
adminId:
users: [user1, user2, ...]
highlightedQuestions: [question1, question2, ...]
}

Questions document

{
uid:
eventId:
userId:
content:
userLikes: [user1, user2, ...]
postTime:
}

Users document

{
uid:
name:
email:
}

Best Answer

There are plenty of questions to consider when choosing the DBMS technology at the beginning of a project. Here are the first two that come to my mind:

  • How easy can the data be modelled ?
  • How will the data be accessed in the future ?

Unfortunately, the information you provide here is insufficient to advise you. Here nevertheless some thoughts that could help you.

1. How easy can the data be modelled ?

From your use cases, I imagine the following entities in your model: Users, Events, Questions (for an event), Votes (for a question), and perhaps Answers (although this is not explicit: nobody answers questions in your use case list).

This is very easy to model in a relational scheme, since the data seems rather structured, with well established relationships.

But it could as well be very easily modelled in a No-SQL document database such as MongoDB: User would for instance be one kind of document, and Events an independent kind of documents. Questions would then be a list of sub-documents embedded in Events.

Hint 1: Before choosing the DBMS technology, make a model of your domain. This will help you to understand the data structures that you'll need and identify the implementation alternatives

2. How will the data be accessed ?

In the relational model, it's easy to browse through the relations. Pick any entity and follow the relations until you have what you need. For example, pick a user, find related questions, find related events; or start from an event, find the related questions and users. Total navigation flexibility.

In the document database, you need to analyse further the main path of access, and trade-offs between update cost and retrieval performance, and make design choices. With MongoDB for example, you would typically wonder if you'd better use a more convenient embedded model or a more performant normalized model.

Hint 2: On your model identify how you'll need to access the data in the different use-cases you've identified. Then imagine how you'll do it in the relational and in the document-based approach.

Hint 3: Now imagine that several user want to ask or update questions. What update conflicts could arise due to concurrency ? How could you cope with it in the different DBMS schemes that you consider ?

Final thoughts

As a rule of thumb, the relational model is very flexible as long as the data is rather structured and in a stable fashion. Personnally, I'd opt for this approach as first choice in your case, but I have no valid argument to justify it.

The "no-sql" engines (in fact the non-relational DBMS, because SQL is just the query langage) are a better choice if the data is less structured, if the structure evolves very often, or in case of specialized data structures.

In this regard, be aware that there are different kind of No-SQL, even if document stores like MongoDB seem to be the more commonly used. There are also graph databases, tuple-stores, column stores, etc... As you see, each kind correspond to some specific needs regarding the data or the way to access it. Would you recognize any such need for your domain ? Answering this question should give you the response you're looking for.

Related Topic