Post/Comment DB design: Postgresql v/s CouchDB

[Meekaa Saangoo <me...@hilarious.com>]

I am comparing DB design for a simple "Post and Comment" system using Postgres and CouchDB.
With Postgres I can design the following tables:

user_info {email, pass_hash, pass_salt, ...}
post_info {post_id, creator_email, title, text, ...}
comment_info {comment_id, creator_email, post_id, parent_comment_id, text, ...}

But if I use CouchDB, there is a concept of creating per-user tables. So I was thinking of the following design:

user_table {email, table_id}
user_<table_id> {email, pass_hash, pass_salt, ...}
post_<table_id> {post_id, <table_id>_creator_email, title, text, ...}
comment_<table_id> {comment_id, <table_id>_creator_email, <table_id>_post_id, <table_id>_parent_comment_id, text, ...}

I am in no way expert in Postgres and CouchDB, so my question is, is this the correct way to design per-user CouchDB tables? What is the better way? And what is the efficient way to create/use CRUD queries?

Thank you!

Re: Post/Comment DB design: Postgresql v/s CouchDB

[Jan Lehnardt <ja...@apache.org>]

Hi Meeka,

we don’t generally recommend a per-user database system unless you deal with replication with e.g. PouchDB where each database needs distinct user access.

For just a regular blog app, you can use a single database. The CouchDB docs explain just that example in fact. You’ll be working with different types of documents in the same database and use views to build 1:N relations like you know them from Postgres:

https://docs.couchdb.org/en/stable/ddocs/views/intro.html
https://docs.couchdb.org/en/stable/ddocs/views/intro.html#the-view-to-get-comments-for-posts
https://docs.couchdb.org/en/stable/ddocs/views/joins.html

Best
Jan
—
Professional Support for Apache CouchDB:
https://neighbourhood.ie/couchdb-support/

*24/7 Observation for your CouchDB Instances:
https://opservatory.app

> On 1. Apr 2022, at 10:38, Meekaa Saangoo <me...@hilarious.com> wrote:
> 
> I am comparing DB design for a simple "Post and Comment" system using Postgres and CouchDB.
> With Postgres I can design the following tables:
> 
> user_info {email, pass_hash, pass_salt, ...}
> post_info {post_id, creator_email, title, text, ...}
> comment_info {comment_id, creator_email, post_id, parent_comment_id, text, ...}
> 
> But if I use CouchDB, there is a concept of creating per-user tables. So I was thinking of the following design:
> 
> user_table {email, table_id}
> user_<table_id> {email, pass_hash, pass_salt, ...}
> post_<table_id> {post_id, <table_id>_creator_email, title, text, ...}
> comment_<table_id> {comment_id, <table_id>_creator_email, <table_id>_post_id, <table_id>_parent_comment_id, text, ...}
> 
> I am in no way expert in Postgres and CouchDB, so my question is, is this the correct way to design per-user CouchDB tables? What is the better way? And what is the efficient way to create/use CRUD queries?
> 
> Thank you!
>