How to structure db where main records are large
NoSQL is not a direct solution to an amount of data problem, rather it is mainly a solution for a schema-less or highly variable schema problem (as you've experienced when you said &quot;we lose a bit of the standard easy relational schema&quot; regarding using NoSQL). Because you admit your data is highly relational, fitting a standard schema, then you'll likely want to continue to use a RDBMS like MySQL.
That being said, millions of rows in a table is on the small side and certainly no concern for a relational database when architected and indexed appropriately. If I play out the calculation with your example's numbers above, it seems like we're actually talking about 100 million records per year, which is now what I subjectively consider a medium sized table. The fact the width of the table is only 10 columns helps keep it compact as well. This is still no problem for any modern RDBMS. I've personally worked with larger tables in the 10s of billions of records, about 50 columns wide, and regular B-Tree indexing still works amazingly well even at that scale. I could query thousands of rows from that table in a second or less and millions of rows from that table in about 10 seconds, on very modest hardware.
Aside from appropriately indexing and architecting your database, you can use things like Partitioning, and routinely archiving old data that is less frequently queried into a separate table. Long story short, it sounds like your architecting things correctly and I wouldn't stress too much on the amount of data you plan to support yet, until it becomes problematic (which I don't think it will).