Postgres or MongoDB

Question

I have to make a website. I have the choice between Postgres and MongoDB.

Case 1 : Postgres

• Each page has one table and each table has only one row, for one page (each page is not structured like another)
• I have a timelined page with medias (albums and videos)
• So I have multiple medias (pictures, videos), and I display it as well by an album of pictures page and a videos page.
• Therefore I have a medias table, linked with an album table (many-to-many), and a type column for determining if it's picture or video.

Case 2 : MongoDB

• I'm completely new to NoSQL and I don't know how to store the data.

Problems that I see

• Only one row for a table, that disturb me
• In the medias table, I can have an album with videos, I'd like to avoid it. But if I cut this table in pictures table and videos table, How can I do a single call to have all the medias for the timelined page.

That's why I think it's better to me to make the website with MongoDB.

What is the best solution, Postgres or MongoDB? What do I need to know if it's MongoDB? Or maybe something escape me for Postgres.

Answer 1

I suggest you use the Clean Code architecture. personally, I believe that you MUST departure your application logic and data access functions aside so they can both work separately. your code must not rely on your database. I rather code the way that I can migrate my data to every database I'd like it would still work. think about when your project gets big and you want to try cashing to solve a problem. if your data access functions are not separated from your business logic code you can not easily achieve that.

I agree with @espino316 about using the project you are already familiar with. and also with @Actung about you should consider learning a database like MongoDB but in some training projects first, because there are many projects that the best way to go is to use NoSQL.

just consider that might find out about this 2 years AFTER you deployed your website. or the opposite way, you go for MongoDB and you realize the best way to go was to use Postgres or IDK MySQL, etc.

I think the best way to go is to make the migration easy for yourself.

all the best <3

Answer 2

I think you are coming to the right conclusion of using a NoSql database because you are not sure about the columns in a table for a page and thats the reason you are creating different tables for different pages. I will still say to make columns a bit consistent over the records. Anyways, by using MongoDB, you can have different records (called documents in MongoDB) with different columns based on attributes of your page in a single Collection (Tables in SQL). You can have pictures and videos collections separately if you want and wire them with your page collection using some foreign key like page_id. Or you can call page collection to get all the attributes including an array containing the IDs of all videos or pictures by which you can retrieve corresponding videos and pictures of a particular page like illustrated below,

Collections

Pages [{id, name, ...., [video1, video2,..], [pic1, pic2, pic78,...]}, id, name, ...., [video1_id, video2_id,..], [pic1_id, pic2_id, pic78_id,...]},...]

Videos [{video1_id, content,... }, {video2_id, content,...}]

Pictures [{pic1_id, content,... }, {pic2_id, content,...}]

Answer 3

It will depend on time, if you don't have time to learn another technology, the answer is to going straight forward with the one you know and solve the issues with it. If scalability is more important, then you'll have to take a deeper look to your architecture and know very well how to scale postgresql.

Postgresql can handle json columns for unstructured data, I use it and it's great. I will have a single table with the unstructured data in a column name page_structure, so you'll have one single big indexed table instead of a lot of one row tables.

It's relative easy to query just what you want so no need no separate tables for images and videos, in order to be more specific, you'll need to provide some scheme.