When building a new system that has a database, there will be a collection of tables that are reference tables. Lists of possible values that contain human readable versions of numeric codes. An example might be a status code for an order. The statuses might be OPEN, PENDING, CANCELLED, SHIPPED. Well written applications will make use of these reference tables to populate the user interface pick lists, thus allowing a new status, like RETURNED, to be added quickly by just adding a row to the database.
| Order Status | |
|---|---|
| ID | Name |
| 1 | Open |
| 2 | Pending |
| 3 | Cancelled |
| 4 | Shipped |
| 5 | Returned |
Of course, that means that those tables must be populated. There are right ways and wrong ways to go about this. The worst thing you can do is to simply sit down at a terminal and start typing INSERT statements right to the database. If you do this, you have no reproducibility. You may have several instances of this database, for instance a test version and a production version. Being able to build an application database from a virgin install is critical. So write scripts, keep them in version controlled files, and keep them up to date. If, somewhere down the line, you do add a status code for RETURNED, you should just update the script for that table and run it.
Which brings me to the part that is frequently overlooked and that messes up a lot of people. Database initialization scripts must be written to be IDEMPOTENT. Idempotency is that property that guarantees that performing an action multiple times has exactly the same result as performing it once. Each table initialization script should be IDEMPOTENT.
This means checking for the table and if it is missing, create it. Check for each record and if it is missing, insert it. Hard code the primary key in your reference script so it will not change. If there is a sequence involved, be sure to reinitialize it appropriately.
The reason for this is that, one day, some idiot will accidentally delete or damage part of your database’s reference data, either through coding error or just mistyping commands. Probably, the idiot will be you. You’ll look like much less of an idiot if you can just whip out your trusty database initialization script and know you can instantly repair the damage because your scripts are all written to be IDEMPOTENT.