Database structure and source control - best practice

Solution 1:

Have a look at this five part series on the principles and practices of database version control (by K. Scott Allen):

1. Three rules for database work
2. The Baseline
3. Change Scripts
4. Views, Stored Procedures and the Like
5. Branching and Merging

The five parts are important but basically the idea is to have a baseline and then change scripts (with a version table). Updating the database means applying change scripts "above" the current version. And this strategy is very VCS friendly (no conflicts).

Solution 2:

We have all our database objects under source control using Visual Studio Database Edition (DBPro). It is a wonderful tool that version controls our schema, does builds, validations, allows code analysis, schema comparisons, deployments, data comparisons, refactoring etc. It was designed from the ground up to be a DB management and version control system. Highly recommended.

This is the blog site of the lead architect for DBPro: click here

Solution 3:

Using a 3rd party SSMS add-in ApexSQL Source Control, database objects can be automatically scripted and pushed to a remote Git repository, or even to a cloned local one, if you prefer working with local repository.

ApexSQL Source Control support Git source control system out of the box. That means you don’t need any additional Git client installed. Besides this, Branching and Merging are integrated and available through the add-in UI.

Solution 4:

Assuming that you use the .net framework, have a look at the Fluent Migrator and also the Hearding Code Podcast that talks about the project.
The main aim as I see it is to easily code the migrations as you do your normal coding using a fluent interface using a database agnostic approach.

It is built on top of the .net framework. and works with a number of database formats including SQL Server, SqlLite and MySQL.

The advantage of the this approach is that it lives with the rest of your code and can therefore be managed by SCM

Example:

   [Migration(1)]   
   public class CreateProjectsTable : Migration   
   {   
       public void Up()   
       {   
          Create.Table("Projects")              
            .WithIdColumn()             
            .WithColumn("Name").AsString().NotNullable()                
            .WithColumn("Position").AsInt32().NotNullable()             
            .WithColumn("Done").AsBoolean().NotNullable();
       }  
       public void Down()  
       {  
           Database.RemoveTable("Projects");  
       }  
   }