NoSQL isn't a very well defined term and all the solutions that run under this name have very different features, so a lot may be possible or not depending on what exactly you are planning to do with it.
Basically you could use some of the more general solutions like maybe MongoDB or Cassandra to simply replace your current relational database. In some cases this makes more sense in others less, but it will work once your team got used to it. Certain things will be easier then, others will be more difficult and you must weight those options against each other and decide (which often enough will mean that there are no advantages big enough and the simple fact that everybody in the team feels most comfortable with relationals and SQL will make the decision easy)
Other NoSQL solutions that are more specialised are not really good candidates to replace your relational DB, like graph databases or simple key value stores. So lets from here talk mainly about those databases that are at least to some degree similar to relational databases.
Scenario 1
Where I work we have exactly this scenario, though quite more complex with a lot of different attributes per article. Some of those attributes in hierarchies like Apple -> iPad -> Air.
The data is still stored in a relational database. But: searching this in real time became a pain. With SQL it was slow and code would have been terribly complex. Selects over many tables, with the additional option to exclude certain attributes like "not blue".
In this case Apache Solr or Elastic Search are a solution. Though of course data is duplicated from the relational database.
But from here our experience with this kind of document store showed that it can handle certain problems very well and we will consider to replace part of the existing relational structure with some other kind of storage. So not the whole database where we also store all the transactional data like orders etc, but for example take out all the attribute information which can be handled much better in the aggregate like data structures of NoSQL.
Scenario 2
Difficult to say, since what you describe is most likely only a very small part of your user handling. Having schemaless storage is an advantage with many NoSQL databases. But some relational databases allow to store such data too (as long as you don't need to query it via SQL in most cases).
Cassandra for example would allow you to define column families in such a case, where your first set of attributes would be one such family and the variable attributes another one.
As somebody said: NoSQL is less about storage and more about querying. So the question is what will be the typical use case for those queries.
A typical problem would be the transactional data here. If you want to store orders, one way would be a schema where users and their orders form an aggregate (kind of user document that contains the orders as subdocuments). This would make getting a user together with his orders very simple and fast, but would make it very difficult to retrieve all orders from last month for sales statistics.
Also strengths of NoSQL solutions are that it can be easier to run them on multiple clusters if you have to work with very large datasets.
Conclusion: Both your scenarios could be modelled with certain NoSQL solutions, but I don't think that (assuming they have to run in a larger environment) they really justify a large extra effort in learning, training and implementation and maybe some other additional disadvantages because both are not specific enough to really leverage the strengths of NoSQL. At least not in that simple form you describe it. Things may become very different once some aspects you describe would be very, very prominent in your usage scenario, like in scenario one the attribute data becomes very complex or in scenario two the variable fields become the largest part of data you store with every user.
Sounds like you have 3 major categories of data you are trying to store:
- General job data (job id, job requester id, job receiver id etc)
- State transitions (job started, job finished)
- State-specific job data
- (optional) job-related events (price changed, job receiver user reassigned etc)
The key is to separate event-like data from everything else.
Schema design
Here are some details:
1. General jobs table
All of the information that's NOT state-specific goes into the (let's say) "jobs" table. Auto-generated primary key: job_id
2. State transitions table
All information about state transitions goes into "job_state_transitions" table, which might have the following columns:
- job_transition_id
- job_id
- created_at
- from_state
- to_state
Ideally this table is append-only. Nothing is every updated or removed here.
Using such a table, you can find out the latest status of any particular job by selecting the latest row for a given job_id from the job_transitions table. You can further denormalize that and introduce a "job_state" column, the contents of which are updated every time a new row is inserted in job_transition table (stored procedures might help here if that's your thing).
You can also do all sorts of analysis on state transitions, because the timing data is preserved (created_at is a date/time field that can help with that)
3. State-specific job data
All of the state-specific data goes into "[state]-jobs" tables. Primary key: some sequence id. Main index: job_transition_id
4. Optional event data
You can also introduce an "audit trail" table that allows you to keep track of various changes that users might request for each job, like your "change agreed upon price". This is a generalization of the state transition table: one main table that contains events and a one supplemental table for each event type (for example, price_changes table with job_id, created_at, from_price and to_price columns).
Scaling to millions of users
If the main "jobs" table grows to be unwieldy, you can shard it by job_id or requesting_user_id or something like that.
Likewise, events table should be append-only and can be rotated or purged of events related to jobs that have been finished.
Best Answer
What you are doing is a good enough approach. I did something similar with doxdb which is a ID mapped to a blob. My blob is a BSON a binary representation of JSON for optimization then have separate tables that get created on updates of the record.
In a sense that’s how Apple mail does it except instead of BLOBs they used message files and built the database on top of it.
A nosql document database like elasticsearch and mongo can be used as your data store and have a MySQL to provide the tables that you can use to query.