So in a traditional database I might have 2 tables like users, company

This is to say that user 1 and 2 are apart of company 1 (a really boring company) and user 1 is the owner of this company.

I could easily issue an update statement in MySQL or Postgresql to update the company name.

But how could I model the same data from a NoSQL perspective, in something like Dynamodb or Mongodb?

Would each user record (document in NoSQL) contain the same company table data (id, ownerid (or is owner true/false, and company name)? I'm unclear how to update the record for all users containing this data then if the company name needed to be updated.

CodePudding user response：

In case you want to save the company object as JSON in each field (for performance reasons), indeed, you have to update a lot of rows.

But best way to achieve this is to have a similar structure as you have above, in MySQL. NoSql schema depends a lot on the queries you will be making.

For example, the schema above is great for:

• Find a particular user by username, along with his company name. First you need to query User by username (you can add an index), get the companyId and do another query on Company to fetch the name.
• Let's assume company name changes often
• In this case company name update is easy. To execute the read query, you need 2 queries to get your result (but they should execute fast)

Embedded company JSON would work better for:

• Find all users from a specific city and show their company name
• Let's assume company name changes very rarely
• In this case, we can't use the "relational" approach, because we will do 1 query to fetch Users by city and then another query for all users found to fetch the company name
• Using embedded approach, we need only 1 query
• To update a company name, a full (expensive) scan is needed, but should be ok if done rarely

What if company name changes ofter and I want to get users by city?

• This becomes tricky, NoSQL is not a replacement for SQL, it has it's shortcomings. Solution may be a platform dependent feature (from mongo, dynamodb, firestore etc.), an additional layer above (elasticSearch) or no solution at all (consider not using key-value NoSQL)

CodePudding user response：

Depends on the programming language used to handle NoSQL objects/documents you have variety of ORM libraries to model your schema. Eg. for MongoDB plus JS/Typescript I recommend Mongoose and its subdocuments. Here is more about it:

https://mongoosejs.com/docs/subdocs.html