I use Postgres 9.6 and Django 1.10, but this may be more generic than the software packages I am using. However due to Django ORM limitations or/and my understanding, it may be dependent on these software packages.
My problem is I want to design Post and Comment tables. As easy as this seems, I will use heavily a query that fetches both Posts and Comments
ordered by date which is a timestamp in both tables so I can have something like post 1hour ago, post 2 hour ago, comment 3 hour ago, post 4 hour ago
Initial design can be like:
Posts:
`id`(serial primary key) | `author`(foreign key) | `title`(string) | `body`(string) | `created_on`(timestamp)
and Comments:
`id`(serial primary key) | post(foreign key to `Posts`) | `author`(foreign key) | `body`(string) | `created_on`(timestamp)
I don't know if it's feasible using native SQL or something like SQLAlchemy, but I think it is impossible to merge both posts and comments ordered by created_on of both tables.
I also looked into desiging posts and comments into something like this:
Posts
`id`(serial primary key) | `author`(foreign key) | `title`(string) | `created_on`(timestamp)
and Comments:
`id`(serial primary key) | post(foreign key to `Posts`) | `author`(foreign key) | `body`(string) | `created_on`(timestamp) | `is_op` (Boolean)
where is_op denotes whether it is the OP (i.e. post) or a comment to the post. This approach makes it easy to fetch both posts and comments since I have only one created_on column. However, this may cause penalty since I show posts much more frequently than comments yet I have to look into the Comments table every time I want to fetch the body of the post, also the body max length requirements are different for posts and comments. Also this approach makes it harder to fetch the count of the replies (i.e. number of comments - 1 which is OP)using Django ORM.
How can I design these tables in a more efficient way that make it possible to get:
- both comments and posts ordered by date.
- annotate (according to Django naming convention) number of replies/likes for any post in one query.
