Hi Theresa, (this will be a long response so please bear with me)
I am wondering what the capabilities of the ThingWorx databases (PostgreSQL or H2) are in comparison to a remote database like MySQL. |
PostgreSQL or H2 are the so called Persistence Provider while MySQL is not (i'm gonna refer all non Persistence provider as external data store), what it means is that if you using one of those Persistence Provider DBs all the data generated/pulled into ThingWorx goes into that DB by default, including the ThingWorx's own data. While connecting to MySQL over the RDBMS Connector would just mean you storing/creating data for some Things, not everything will be stored in MySQL rather only the data belonging to that Thing which is configured to store in MySQL DB. As to the capabilities of MySQL I think you can get that out of PostgreSQL too. You might want to visit this blog for comparison if you haven't already Comparison of ThingWorx Database Options (taken from KCS Article CS257403)
and of course Where Should I Store My Thingworx Data?
Is it correct that ThingWorx is not able to model a database structure (with foreign keys)? |
There are relations created and used internally they may or may not be mapped 1:1 to a foreign key concept of RDBMS. But if that's what you are more comfortable and is your business need, you sure can take it to the external data store like MySQL. For sure ThingWorx isn't storing everything in a table so there's definitely referencing going on in the background you might want to check these Programming for the IoT & Thing-Centric vs Traditional Query Approaches
Therefore, is ThingWorx not able to query across multiple tables with one query? |
You can create SQL queries within ThingWorx which can of course lookup several different tables in an external data store. If you are storing everything in the DataTable, which in turns store in the Persistence provider, you can still call or reference other DataTable data via Java Scripting. Even in generic JS services you can combine calls to several other Things or call other services belonging to other Things.
Under which conditions is it better to use a remote SQL database? |
There could be several scenarios to pick from to have an external data store, one could be where you already have your existing organizational data which you want to utilize/combine with ThingWorx in such cases you can simply connect the external data store to ThingWorx over RDBMS Connector or if you need to run extensive analytics for on the data directly before pulling that into the UI like ThingWorx's Mashup. This way you are crunching the number as close to the data as possible, even utilizing your existing SQL scripts (something on that here Accelerate analytics and transactions on external data store for ThingWorx using Oracle 12c In Memory Column Store featu…)
Contrary to that if you are starting afresh and it's expected to handle very large amount of data coming in streams like so from EDGE devices you can go with Persistence Providers like DataStax Enterprise, MS SQL, SAP HANA or High Availability mode on PostgreSQL
Hope this would help iron out some wrinkles.
