Designing document - migration form a SQL database

What would be the best bucket “Design” for following use case:

                  1-N                       1-N
 +--------------+       +----------------+      +--------------------+
 | Book         |--+    | Lecture        |  +---| Word               |
 |--------------|  |    |----------------|  |   |--------------------|
 | -id          |  +----| - id           |--+   | - id               |
 | -name        |       | - name         |      | - label            |
 | -changed     |       | - changed      |      | - changed          |
 | -user_id (fk)|       | - book_id (FK) |      | - lecture_id (FK)  |
 +--------------+       +----------------+      +--------------------+
         |
         +-+   N-1
           | 
    +-----------+
    | User      |
    |-----------|
    | -id       |
    | -name     |
    +-----------+	

Would It be better have just a User document and a Book document where the book
document would contains all lectures and words or have document-per-table?

  1. User and Book = I see a disadvantage with updating. When needs to be update just one
    word also needs to be update whole document (which might contains +1000 words).

  2. document-per-table = Almost every time when needs to be updated a word is necessary also
    check whether a user who made this change has a permission to perform the update. Would it be better to have user_id also stored within every document, or rather do joins book and lecture table?

Which design would be better for given use-case? Data also will be synchronized with mobile devices (2-way).

Thanks for your feedback

do you use Couchbase Sync Gateway?

Yes, I would like to use it, but in the second phase.

That depend on how you design Channel and ACL in SyncFunction if you want to use Sync Gateway.
for SyncFunction,you can use requireUser() requireRole() requireChannels() to check whether a user who made this change has a permission to perform the update.
so in your document,you should include user_id or channel info to limit user to change the document.

1 Like

See

There is a data modeling section in the workshop.

2 Likes