1. Documentation

When it comes to the relational database, software maintenance is way more costly than its creation. That’s just how it is. As a professional, you want to have your customer happy. What’s more, you want everybody working on the software, including you, be as effective as possible. What can you do about it? It’s pretty easy, you need to find out why this column is here, and it is named like that. Once you do that you need to pass this information can before you’ll move on to a new project. Some tools will aid you in that task:

1. When you miss some documentation, Redgate SqlDoc is good to add it fast.
2. SchemaSpy has nice, interactive diagrams that you can browse in the static HTML sites generated by the tool. It helps you navigate and to see where you need to put some more work in the documentation. Then just share it with the team. If you don’t mind difficult setup, you can work with for free as it is an open source project.
3. Dataedo is similar to the tool above. It is commercial software, but also more polished than SchemaSpy.
4. If you’re looking for something simpler, this gist makes you a two-way source-controllable / editable list of your documentation in SQL Server.
5. SQL Schema Explorer generates dynamic HTML sites. It makes it easy to browse the documentation and allows you team sharing.

2. Refactor your database

Nowadays, database migrations are becoming popular. I advise you to use them. Since everybody is refactoring code, why not do the same with databases? Nobody benefits from any traps you leave. Confusing names, messed up schema will only waste people’s time. I recommend using Redgate tools like ReadyRoll, or ORM tools.. I admit you have to work with data, but it’s highly unlikely it will take hours to run because of data volumes.

1. Redgate's SQL Change Automation (you may know it as ReadyRoll) is well known and used by many tools for creating and running database migrations. Furthermore, it is able to generate DBA friendly pure-SQL deployment packages. That’s without a doubt pretty useful.
2. Redgate's SQL Source Control also has support for migrations.
3. I work with Entity Framework Core migrations and they work well for me. There are similar solutions for other languages.

3. Enforce data integrity

Is your application crashing when having a problem with data? Databases can take care of the integrity of your data for your code with constraints - unique keys, foreign keys, nullability, data length etc. You want to prevent bad data from getting into your database and not have to worry about it in your code.

4. Integration testing

If you have an ORM and some unit tests it’s great. But in real life scenarios your SQL can break when it mess real database and data. You need to take the first steps towards the automation of creating, testing, destructing your database. A run complete integration tests. I advise to automation of testing of logic and data access to make tests realistic to run. There’s a number of ways you can keep tests quick:

1. end to end smoke tests instead of individual pieces,
2. use an in-memory database,
3. use database snapshots or Redgate SQL Clone tool to make creation and rollbacks virtually instant,
4. pull real (but anonymized) data from production

5. Make it visible

I’m curious, who is able to access the DB structures in your company? Only IT people? What about business, support staff, QA or data analysts? They should also have a chance to be able to understand what sits there. Make your databases as crystal clear as possible so you don’t have to be embarrassed. Allow everyone to be more productive working with data.

Share the design of your database with one of the tools available. Here are some examples:

1. SchemaSpy - it can generate an HTML that can be shared on any web server.
2. The same goes for Dataedo. It also works pretty well with Confluence.
3. SQL Schema Explorer has an option to run on your network or cloud hosting (schema explorer is dockerized!). Since it has the dynamic HTML, your team can browse both the schema and data in the DB.

Now, pick up some tools from the list, combine them with a continuous integration system, and give access to all the people that can benefit from the information, not only the technical staff.

Start today!

1. Don’t wait with the improvements until forever. Make the first step today.
2. Share this article with your team – the more people will be engaged, the faster the changes will happen.
3. Share this article on social media – be a spark that will light the fire that our databases need more attention!

I hope you’ve learned something today and I convinced you to shed some light on the poor, sad databases that simply needs some more love to give you back a lot more.

Originally posted at:

http://schemaexplorer.io/blog/2018/07/10/5-ways-to-make-your-database-better.html