I am facing a conceptual development methodology problem that I was hoping you could help me with. Firstly, a little bit of background.
The project I am developing is a niche application that contains a database of information, and the target audience for my application are private individuals on the lower-end of the technical proficiency scale. In fact, that is the primary reason for my project: many similar options in this field exist but the vast majority are fragile and complicated applications that require a high degree of both programming, database and sysadmin experience just to keep running. Worse yet, years or even decades of poor maintenance and "improvement" by amateur programmers who only barely understood what they were doing means many of these applications are simply beyond that ability of a non-professional to maintain.
The reason that I began this project was to give them a low barrier of entry alternative that they could reasonably extend and customise via configuration rather than programming, to centralise and minimise the maintenance. I myself have been programming for nearly 20 years but not until very recently in a professional capacity: I am actually a Civil Engineer, but my main professional experience is in being a client/technical representative for large enterprise engineering and asset management systems. So please excuse me if I am not 100% up to date with all of the professional or academic lingo.
My big conceptual problem is how to safely and invisibly to the end user merge database changes as part of an upgrade process. Basically, as I release major versions, sometimes extensions to tables or new tables might be made to the database model, which will need to be rolled out as part of the patching process. In the past, I have only ever done this on my own development servers and my approach was fairly haphazard and manual.
How should I go about quantifying, preparing and deploying the required database changes as part of my application? For reference, my program is written in C# and I use SQL Server (deployed to the user bundled with the application as SQL Server Compact Edition). I have not yet selected a patching platform to use, as possible compatability with whatever methodology I come up with here may influence my choice. My thoughts are that when I decide to do a release, there are probably some tools I must use to prepare a database difference between the last release and the current release, and prepare a SQL script to execute. Then at some point in the installation process, it must run the SQL script and bring the database up to date.
It seems simple, but I am really not sure whether it is possible. Most of the tools I can find for databases out there seem aimed at Enterprise-level applications and Database Administrators, and I am hard-pressed to figure out if they can actually do the simple task I need it to do.
Am I on the right track for doing this or is there a better approach?