I’ve been working on a project for a little while and I’m unsure which is the better architecture. I’m interested in the consensus. The answer to me seems fairly obvious but something about it is digging at me and I can't pick out what.
The TL;DR is: how do you handle a program with application data and user data in the same DB which needs to be able to receive updates to the application data periodically? One database for user data and one for application, or both in one?
The detailed version is.. if an application has a database which needs to maintain application data AND user data, and the user data all references application data, it feels more natural to me to store them in the same database.
But if there exists a need to be able to update the application data within this database periodically, should this be stripped into two databases so that one can simply download the updated application data database file as an update and replace the old one? Or should they remain as one database, and the application data be updated via a script which inserts the new data into the existing database? The second sounds clearly preferable to me… but for some reason just doesn’t feel right, and I can't pick out quite why.
Best Answer
If you ever need to move the application, independently of the user database, then you need a separate database for the application (in whatever form that takes), so that the database can travel with the application, leaving the user data intact in its original location.
It therefore follows that, if the application database is updated periodically from the vendor (that's you), then it needs to be kept separate from the user's database, so that you can distribute changes to the application database without affecting the user database.
Now, if you need to add fields or tables to the user database, that's a different story. For that, you need a module that can accept as input a table of changes from the application database, to be applied to the user database. Some programs do this by "converting" the user database to the new format.
Data conversion can be done by using SQL DDL to apply the field and table updates to the user's database, in a way that doesn't negatively affect the user's data. In some advanced scenarios, data transformations might actually take place; normalization or denormalization, for example.
If you need to provide users with the ability to do a data transfer, you should use some other mechanism such as a communications conduit, or an import/export file containing the data to be transferred (perhaps in XML).