Database Design Decision - Externalizing table data for apps in another RDBMS or same

[avillanueva]

We had some custom apps developed and one of the design decisions was to use data tables for some records but others were too vast, so they externalize those tables in another table. In reality, they created a custom table in the Thingworx database with its own connector. My question to field was this a wise decision? Should custom data tables exist separately from the main tables that drive Thingworx? What is best practice here? 

[Rocko · Accepted solution]

Depending on the RDBMS used, the terms "database" and "user" and "schema" might be coined different, but don't put it in the same schema the TWX tables are in. You can create an own user/schema on the same database and put it there. 

This way you don't have to "reuse" the thingworx schema DB account and password, you also have clean separation. If Thingworx ever needs a new table, and it accidentally uses the name your own tables has, you're in trouble. 

Also, this gives you more freedom in moving the table to another schema (or database) if needed.

[avillanueva]

Thanks. What I figured. Easy enough to migrate since they did build unique integration connectors so snap to shift off. Another task on the to do list.