I am wondering what's the easiest method to create and keep databases maps and SQL scripts. I understand I am more keen on writing the SQL script manually on the text editor (plus creating a roadmap to possess alongside me on the sheet of paper), while some favors to make use of database maps softwares (MySQL Work bench, Visual Studio... some indexed by this post) to instantly create the script.

Both solutions have advantages and disadvantages. I see these advantages:

  • SQL script manually:
    1. You realize exactly that which you write.
    2. You'll be able to have a neat and easily readable SQL code.
    3. Writing the code, you retain a much better look at your database specifics.
    4. Writing the code allows you to definitely to practive your SQL understanding.
  • Instantly produced script from designed map:
    1. Saves a while.
    2. Even not understanding SQL language, you will get a script (even when I listed that one being an contrary advantage for SQL script manually).
    3. Prevents typos.

What is your opinion, what way would you proceed?

If automating something saves some time and human error thinking about get it done? I'd try auto-producing SQL if:

  1. I'd already populated an evaluation database and developed the schema within an organic fashion by writing and performing random SQL non-queries that we had not stored tabs on and
  2. When the database was reasonably easy and having a low quantity of normalisation

Another rare situation may be should you have had an enormous listing of data sets that the schema must be removed as SQL.

The advantages of writing manually are that you're conscious of every area of the design and able to better enforce data integrity (for example by way of foreign secrets or restricting data types). In a nutshell, I'd accept your listing of pros.

For upgrade scripts I strongly prefer writing these manually because they are usually fairly short and it is nice to allow them to become more readable for anyone who finish up running them.

One tip I'd have for upgrade scripts would be to make certain each script adds a row stating the upgrade version number inside a table that is solely there to guarantee the database is versioned properly. Something similar to:

INSERT INTO DB_VERSION (upgrade_time, version_from, version_to, comment)
    VALUES ('2011-07-04T120320', '2.2.4', '2.3', 'add column x to table y.')

Handwriting may be the way! :D
A masochist way, however the better method to write code. )