Designing a key-value database

Question

I am trying to design a key-value database .To learn I am trying to build a sample blogging application. I am storing created posts against an id

key : postid
value : post

Now I need to add comments to these posts , I would essentially want to to be able to retrive all comments for a particular post .I can think of two ways to do this .

create a comment id

Key : comment_id , Value : comment

Iterate over all comments and return where required postid == comment.postid ( comment would have a field called postid)

Create the key of the form postid_commentid.

Iterate over the keys , break the key into two using the seprator and match something lik key[0]==postid

Which one would be more efficicent ?
Which one would be more scalable ? I am designing this for practice ,eventually I might want to scale out as well .
Is there any other way that I havent thought about ?

Excellent question by the way. You are on the good track with the second solution, but you need to search or search_near basically jump the cursor to a position that will help you "scan" only the relevant keys. Just like with SQL, full table scan are not good. — amirouche

Thorsten Kettner Thorsten Kettner · Accepted Answer · 2020-12-21T01:20:48

A typical key/value table does not consist of only two columns.

+-----+-----------------+-------------------+
| KEY | WHAT            | VALUE             |
+-----+-----------------+-------------------+
|   1 | POST_CONTENT    | I am Superman.    |
|   1 | POST_DATE       | 2020-12-01        |
|   1 | POST_POSTER     | Mr. X             |
|   2 | POST_CONTENT    | I am tired.       |
|   2 | POST_DATE       | 2020-12-05        |
|   2 | POST_POSTER     | Mr. X             |
|  22 | COMMENT_POST_ID | 1                 |
|  22 | COMMENT_CONTENT | This is not true. |
|  22 | COMMENT_POSTER  | Mr. Doubtful      |
|  22 | COMMENT_DATE    | 2020-12-17        |
|  23 | COMMENT_POST_ID | 1                 |
|  23 | COMMENT_CONTENT | Wow!              |
|  23 | COMMENT_POSTER  | Lois Lane         |
|  23 | COMMENT_DATE    | 2020-12-21        |
|  24 | COMMENT_POST_ID | 2                 |
|  24 | COMMENT_CONTENT | Sleep well!       |
|  24 | COMMENT_POSTER  | The Sandman       |
|  24 | COMMENT_DATE    | 2020-12-21        |
+-----+-----------------+-------------------+

You can even make this four columns, i.e. split what = POST_CONTENT into entity = POST and attribute = CONTENT, which makes this more readable and maybe easier to access. I would do this. You could even call the two columns table and column, because this is what they represent after all :-)

Anyway, key/value tables are a nuisance to work with. It may be a good practice, but you would avoid them whenever possible in real live.

If you used tables with proper columns in an RDBMS, you'd have it guaranteed that the dates contain dates (and not, say, '2020-02-30' or even 'Some day last year'), that a comment must refer to a post and can only refer to an existing post ID and that each post and comment does have a content, data, and poster. With a key/value table none of these can be guaranteed by the DBMS.

In a NoSQL DBMS on the other hand, a post would typically be stored completely with all its data and list of comments. No separate tables, no separate columns, but rather a table of post sheets.

Designing a key-value database

1 Answers