I am designing a global ERP / scheduling system that has to be hosted on Google Cloud SQL and Google Datastore.
For the most part the data is strongly relational and not large in volume or volatile, so is a natural fit for a relational database.
The current design has addresses stored locally in the customer, contract, employee, site, workorder, contractedSites tables.. about 12 different places in total. I requested the DEV team modify the design to create an ADDRESS table that can be referenced by all other entities (with linking tables if necessary to provide flexiblity for address history, multiple addresses, etc).
THE QUESTION - As the address / contact details format wildly differs for all these scenarios, different countries and multiple phone numbers etc, I was thinking that the ADDRESS & CONTACTDETAILS should be moved to the DataStore and reference them by id's from the Cloud SQL platform. So the structure is completely flexible to accomodate the address formats of the world.
Does this sound sensible or is it over engineering the solution and should go with a generic address table in CLoud SQL only?
I have a concern as the bottleneck in the Google platform appears to be the single MySQL master.. This is a managed service limited to 16 vCPU's so I am attempting to move more functional areas into the Datastore.
Hope that makes sense!
You certainly could do that and wouldn't be the first person to run a mixed database system. You will be adding complexity though and there are several things you should weigh before proceeding down that path.
You mention the 16 vCPU limit - it's unlikely that addresses would be the reason to be worried about this, although Cloud Datastore would scale more easily (as in, we do it for you)
For data with a more diverse schema requirements, since you are already using Could SQL would could simply use the JSON type to store it. As long as your are on Cloud SQL Second Gen.
Transactions: If you need operations that involve data in both Cloud SQL and Cloud Datastore to be transactional, you'll have to implement that yourself. If you can live with potential inconsistencies and/or clean-up, no problem.
Cloud Datastore would definitely allow you a lot more flexibility to store this type of data and query it efficiently at scale. It's the sort of use case it excels in.