There are a lot of different techniques for maintaining your database schema. My favorite, although not my most used method is to diagram first. I prefer to use a database diagramming tool, it doesn’t really matter which one, and diagram a complete database. This includes lookup tables, often annotated in the diagram with representative values.
This method works really well when you are doing a complete makeover of an existing database, or if you are trying to get a estimate for fixed price projects, or using a waterfall SDLC. The problem with this method is that you can spend too much time diagraming, and less time solving software problems.
In contrast, there are tools such as DB Maestro, or database solutions in Visual Studio, allowing you to take a database first approach to your design. In this model, you focus is on developing the actual database, and database code. You work in the database, producing database artifacts, and the tool generates the necessary migrations to change you database from a pervious version to a newer one.
A third contrast is one that has gained momentum in the Microsoft community, using Code First migrations. Since you must keep Entity Framework synchronized with your schema, this model will map your domain objects in a fashion that creates database objects from your domain objects and mappings. The intention is to get the basic database at a lower cost.
Many people I talk with feel that this kind of database development is too wild; too out of control. Every developer is doing what they need without focus on the bigger picture. Moreover, how do you manage multiple concurrent projects that may both address the same database? Micro Services?
What’s your solution? What’s your experience? What is your advice? Please leave a comment or drop an email to btaylor@sswug.org.
Cheers,
Ben