How to maximize engineering/feature flexibility when using MySQL?

We are using MySQL as our database, because we are not quite ready to use one of the pure NoSQL databases yet.

However, we are very focused on flexibility engineering different feature sets, and we do not like the rigid constraints than MySQL was mainly built for (columns and foreign key constraints)

As such, for features that are subject to big changes, we are thinking of several ways - for example, storing JSON in blobs, using a centralized relationship table, writing various views in code rather than in the database.

Would love to hear similar examples: i am sure开发者_StackOverflow中文版 this is not a common problem

---

Rather than designing your database differently, I would suggest focusing on the code at the level where you have a method to return some object that the calling code needs. Make sure that all writes go through the same layer also, as with NoSQL you may need to update an index/view on write rather than having it happen automatically in the database.

If necessary the table this layer reads from could be full of json blobs, though I wouldn't recommend that initially - The structure likely won't quite match whatever NoSQL solution you end up with, and you will find a lot more information on migrating from standard SQL design than from a custom system. Also, if your NoSQL platform can search json fields like SQL can search columns, any effort put into searching/filtering those blobs will be wasted.

Similarly writing views in the code is not the best approach - It will not be as efficient as a direct query, and while NoSQL platforms handle that sort of thing quite differently there is usually some feature in the platform that you can take advantage of.

---

Many large websites that are using a NoSQL solution - or even an RDBMS that looks shockingly like a NoSQL solution (eBay, MySpace) - started using a relational database and they designed it to fit their operational needs. As their applications scaled, they made changes to the datastore so it could continue to serve their operational needs.

Some of the items you describe - e.g. a centralized relationship table - can cause massive I/O problems when every operation needs to read that one table to produce a result.

Rather than supplant the features of an RDBMS with your own code, design your application as a set of features and find the best solutions to implement them. There is some core data that is best suited for being in an RDBMS with its "rigid constraints" - one of the creators of Cassandra even thinks this way. It's very possible and fairly easy to have flexible development using an RDBMS, that's one of the problems that ORMs and database migrations solve.

---

In my bookmarks I found this article about some website switching successfully to an EAV structure.