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.