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

Question

Let's say there is an application that must create its own tables in the main database, if they are missing (for example, the application is launched for the first time). Which way to make it more flexible, scalable and, say, more suitable for a commercial product?

If I encode everything, no additional files (scripts) are required. The user will not be able to do something stupid with them, and then complain that the application is not working. But when something changes in the db structure, I have to encode part of the fix, and the user will have to install a new binary (or just replace the old one).

A script solution would be a few lines of code to just run all the scripts from some directory and a bunch of scripts. Binary files may be the same, the fix will be applied automatically. But new scripts should also be deployed to the user at some point.

So what would you recommend?

The application will be encoded in C #, the database will now be under SQLServer 2005, but may change in the future. Of course, drawing the application and the database processing component can be divided into two binary files / assemblies, but this does not solve the dilemma of my code and scripts.

Answer 1

Wizardby: ( SQL DDL), :

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

version - , .

Wizardby , : . , .

: Wizardby SQL- , .

Answer 2

, . , , , , . , , :

• .

• - , , , , . script, .

• , . . , , .

, , .

Answer 3

, DBGhost DVC, , .