What are the prevalent best practices for managing database of an application?
One option is to have an sql script to create the empty tables, and also to start with hardcoded options. (Say all locations, or profession types). During upgrade, you know whats in there and whats not, so on the server, run an sql script that adds columns and any new data.
Another is to do this programmatically, where everytime the server runs- it checks if the db exists, and if not, create it. And during addition of new columns- there would be code that would check if a certain column exists in this schema, and if not add it with a default value of my choice, and also any new data that should be needed.
My constraints are:
- Should be blindly able to restore a backup if anything goes wrong.
- Minimal manual intervention
- I'm planning to run it 24/7 Under addition of tables and columns: By upgrading the SQL database first, and then the application code for corresponding fields. Under removal of tables/columns: By downgrading the application first, and the SQL database.
- I'm not using an ORM.
Am I missing things here that I should add to the constraints? Please clarify your reasoning in relation to the answer.
Also, I hope that since I'm asking for a constrained answer along with sources and reasoning, this wouldn't count as a opinion based question.
EDIT: In Added constraint(3) in response to comment.