How to organize SQL legacy Databases

This post was part of a series talking about How to create a good architecture for SQL Databases

Starting the second part about working better with legacy databases to create good architecture. Previously, I presented general information on how the database has a very archaic design structure in many corporations and presented a simple tutorial to versioning database with GitHub, and now, I will present some tips on how to create a good modeling structure and creating Jobs and Procedures to help in this process.

Database Structure

Taking a look at many Database projects, it seems that they have one responsibility: Present and store in a performance manner the greatest possible number of information that the customer needs and are related. In fact, this is the main goal in having as support a database, but it seems that factors such as organization, consistency, adequacy of the normal forms, structure of the data and legibility of procedures are not much considered, after all, as It is usually the responsibility of a DBM to take care of it, ultimately "nobody cares".

But This approach ends up creating 2 problems, or we will create a monstrous database which it requires a lot of rework so that it is possible to take advantage of it afterwards, or a watch bomb that will explode in situations that require a Good performance, this happens because unless we have a specialist in SQL, Oracle, MySQL, MongoDb or other database servers, we rarely take advantage of the resources that these technologies give to facilitate the work, so I will show some Examples in SQL to help not create such terrible Database.

1. Schemas

A very good resource to organize a database is the use of Schemas to facilitate the understanding of what we are seeing. Basically, it allows separating the database into multiple areas, making it possible to put the database into multiple servers, giving flexibility and more performance to our applications. In addition, Schemas make it much easier to read the tables because they are more grouped.

The Next example allows me to see which tables belong to which domains (People, Human Resources, auxiliary Tables as Logs).

In Addition to using the CamelCase help legibility, the important thing at this point is to have a pattern.

2. Complex Structures

Nowadays we are online and increasingly dependent on patterns like JSON and XML, creating a mix of NoSql and SQL data formats. It's common that there are columns in our databases that do not contain only primitive data such as text and numbers. Product Descriptions can be in HTML and XML, blob Images, query Results API in JSON are some examples of how complex data can be pertinent to only one column of a table.

These formats are about to stay, so working with JSON and XML can make your bank much more organized. Some Examples of new formatting uses:

• Simplify complex data models
• Store retail and e-commerce data
• Process log and telemetry data
• Storing semi-structured data from IoT
• Simplify the development of the REST API

3. Remember the normal forms

Over time, we find it natural the process of creating a database that fully meets us, and with that, we can forget the importance of normalization of a database.

4. Structure Your Stored Procedures And Jobs

Many Stored Procedures end up being created in any way, without many programming rules and with greater focus on processing the data quickly, which is a high risk for any project. Then some tips I saw to use procedures:

• Use simple condition and repetition structures such IF,ELSE and WHILE
• Remember to use TRY,Catch,Commit,Rollback to prevent errors
• Use Print to record which condition structures are being executed.
• Comment code as most as possible

Having a good structure in your code facilitates evolution and maintenance, so it is important to know and utilize as much of the server's possible resources, for example, there are other SQL features that are useful, such as using Views Triggers,Linked Servers, and File Tables. The important thing is to understand that a database done in any way ends up giving many problems, in the next part, I present how to debug SQL Databases.

Useful sources:

• https://stackoverflow.com/questions/45141673/what-exactly-is-database-schema
• https://stackoverflow.com/questions/246701/what-is-normalisation-or-normalization