How to change database design in a deployed application?

Question

Situation

I'm creating a C#/WPF 4 application using a SQL Compact Edition database as a backend with the Entity Framework and deploying with ClickOnce.

I'm fairly new to applications using databases, though I don't suspect I'll have much problem designing and building the original database. However, I'm worried that in the future I'll need to add or change some functionality which will require me to change the database design after the database is already deployed and the user has data in the database.

Questions

1. Is it even possible to push an updated database design out to users via a clickonce update in the same way it is for code changes?

2. If I did, how would the user's data be affected?

3. How is this sort of thing done in real situations? What are some best-practices?

I figure that in the worst case, I'd need to build some kind of "version" number into the database or program settings and create some routine to migrate the user's current version of the database to the new one.

I appreciate any insight into my problem. Thanks a lot.

Answer 1

There are some 'tricks' that are employed when designing databases to allow for design changes.

Firstly, many database designers create views to code against, rather than coding directly to the tables. This allows tables to be altered (split or merged, etc) while only requiring that the views are updated. You may want to investigate database refactoring techniques for this.

Secondly, you can indeed add versioning information to the database (commonly done as a 'version' table with a single field). Updating the database can be done through code or through scripts. One system I worked on would automatically check the database version and then progressively update the schema through versions in code until it matched the required version for the runtime. This was quite an undertaking.