One of the humbling things about working at Oracle with the various MySQL personnel is that you are often blown away by something one of them says or does. And that is on a regular basis. In this case it is Dr. Charles Bell who gave a great series of presentations last June at the Southeast Linuxfest.
In particular he presented in a full formed state some ideas that had been rattling around in my skull (but no way near as coherent) on how to take advantage of the MySQL JSON data type. Below are his points from his slide deck. I was reviewing my notes from his presentation when I realized that this information really needs to be more widely disseminated. And I would like your feedback on these ideas?
1.. We can use a JSON field to eliminate one of the issues of traditional database solutions: many-to-many-joins
- This allows more freedom to store unstructured data (data with pieces missing)
- You still use SQL to work with the data via a database connector but the JSON documents in the table can be manipulated directly in code.
- Joins can be expensive. Reducing how many places you need to join data can help speed up your queries. Removing joins may result in some level of denormalization but can result in fast access to the data.
2. Plan For Mutability
- Schemaless designs are focused on mutability. Build your applications with the ability to modify the document as needed (and within reason)
3. Remove Many-to-Many Relationships
- Use embedded arrays and lists to store relationships among documents. This can be as simple as embedding the data in the document or embedding an array of document ids in the document.
- In the first case data is available as soon as you can read the document and in the second it only takes one additional step to retrieve the data. In cases of seldom read (used) relationships, having the data linked with an array of ids can be more efficient (less data to read on the first pass)
This presentation and others from the Southeast Linuxfest will be available online and I will be sure to post about that when it happens.
And a big thank you to Dr. Chuck for these ideas.
