What approach for creating and editing database structure: code or scripts?

Question

Let's say there's an application which should create its own tables in main database if they are missing (for example application is run for a very first time). What way of doing this is more flexible, scalable and, let's say, more suitable for commercial product?

If I code it all no additional files (scripts) are needed. User won't be able to make something stupid with them and then complain that application doesn't work. But when something will change in db structure I have to code patching part and user will have to install new binary (or just replace the old one).

Scripting solution would be a few lines of code for just run all scripts from some directory and bunch of scripts. Binary could be the same, patching would be applied automatically. But new scripts also have to be deployed to user at some point.

So, what would you recommend?

Application will be coded in c#, database for now will be under SQLServer 2005 but it may change in the future. Of course, drawing application and database handling part can be separated into two binaries/assemblies but it doesn't solves my code vs. scripts dilemma.

Answer 1

Check Wizardby: it provides a special language (somewhat close to SQL DDL) to express changes to your database schema:

migration "Blog" revision => 1:
    type-aliases:
        type-alias N type => String, length => 200, nullable => false, default => ""

    defaults:
        default-primary-key ID type => Int32, nullable => false, identity => true

    version 20090226100407:
        add table Author: /* Primary Key is added automatically */
            FirstName type => N /* “add” can be omitted */
            LastName type => N
            EmailAddress type => N, unique => true /* "unique => true" will create UQ_EmailAddress index */
            Login type => N, unique => true
            Password type => Binary, length => 64, nullable => true

            index UQ_LoginEmailAddress unique => true, columns => [[Login, asc], EmailAddress]

        add table Tag:
            Name type => N

        add table Blog:
            Name type => N
            Description type => String, nullable => false

        add table BlogPost:
            Title type => N
            Slug type => N
            BlogID references => Blog /* Column type is inferred automatically */
            AuthorID: 
                reference pk-table => Author

These version blocks are basically changes you want to be applied to your database schema.

Wizardby can also be integrated into your build process, as well as into your application: on each startup in can attempt to upgrade database to the most recent version. Thus your application will always work with most up-to-date schema version.

It can also be integrated into your setup process: Wizardby can generate SQL scripts to alter database schema, and these can be run as part of your setup process.

Answer 2

I would usually want to keep my installation code separate from my application code. You will definitely want your application performing some sort of version check against the database to ensure it has the proper structure it requires before running though. The basic setup I would follow is this:

1. Use scripts with each released version to make schema changes to the deployed database.

2. Have something in your database to track current version of the database, perhaps a simple version table that tracks which scripts have been run against it. It's simpler to look for the version script than to check the schema every time and search for all the tables and fields you need.

3. Have your application check the database version marker to ensure it meets the application's version. Log an error that will let the user know they have to update their database with the database scripts.

That should keep your application code clean, but make sure database and app are in sync.

Answer 3

Then using a versioning tool like DBGhost or DVC would help you to maintain database updates and could be modified to fit seamlessly into your application.