How to develop a database (workflow)?

tl;dr: I know a few SQL semantics and I know how databases work (basically) but I never created one that had a considerable size. I lack of knowledge concerning workflow, SQL-code management and very basic SQL programming practices (write by hand or use e.g. pgAdminIII). I need some "instructions" on how to manage my source code and state of my database.

Okay, this sounds like a very basic question and in fact it is a very basic question.

Five years of computer science and I never really had to develop a database by myself and now I am here and have no idea how to do that. Now, as a software developer writing Java or C or whatever the workflow is pretty clear of course. Thinking about design, creating some files, write code, use version control and commit/push. Next day repeat.

My problem is that I can't get my head around that incremental nature of creating a database. What I mean is that I don't know how my SQL script should look like if I commit it to my repository and if I use e.g. something like FlywayDB.

At the beginning I'd have something like this e.g.

DROP TABLE IF EXISTS company_employee;
CREATE TABLE company_employee (
    id BIGSERIAL PRIMARY KEY
);

and I need that DROP TABLE IF EXISTS because while I am developing I might change that table all the time and therefore drop it each time before I recreate it. But do I want to commit that script?

I mean if somebody accidentally runs it on my production server it would drop all my tables and re-create them. So one question I'm asking is e.g. how do my scripts look like that I am actually committing to my e.g. git repository?

Do I have different versions of that file? One that is e.g. a "development"-version and another is a "I am confident it doesn't break anything"-version that I commit?

Another option could be that I e.g. just create the tables in the first step:

```
-- V1__create-company-tables.sql

CREATE TABLE company (
id BIGSERIAL PRIMARY KEY
);

CREATE TABLE company_employee (

I can only tell you what we do now and what I have done in the past. Our shop uses MSTFS (Microsoft Team Foundation Server), so any version we have the ability to get the latest version of the script.

When we create the scripts we include the customary header - Description, Date, Author, Title, and sometimes Build number. This helps us to maintain code versioning and build numbers with database code.

We branch SQL code with functional code so as we integrate branches from developers, their SQL code comes with. When we moved to production, it all moved together.

In other positions, the data group worked with developers to create the database ERD. Then the data group came in as another branch to the project and create database objects. When the functional code was complete and the move was going to production, their branch was deployed to the data tier while the code was deployed to the app tier. The members of the DEVOPS group tied it all together.

There are many ways you can go. You just have to pick one. I suggest reading further on Application Lifecycle Management (ALM).

Typical ALM treats DB versioning like an afterthought if at all. Many companies treat the DB in the same fashion.

The answer is, it depends. I use a tool (MySQL Workbench, VS) to script my database object creation and I version the scripts based on build number. Typically it's YYYYMMDD#. Most DB tools are rudimentary so your mileage may vary. Try some different methods and find what works for you.

StackExchange Database Administrators Q#115964, answer score: 3