The Other steps are as follows:
Starting the second part about working better with legacy databases to create a good architecture. Previously, I presented general information on how database have a very archaic design structure in many corporations, and presented a simple tutorial to work with versioning database with GitHub . Now, I will present some tips on how to create a good modeling structure and creating Jobs and Procedures to help in this process.
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 in fact 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".
the other side of the coin
But This approach ends up creating 2 problems, or a monstrous database in 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 server, 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 Rs.
A very good resource to organize database is the use of Schemas to facilitate the understanding of what we are seeing, The Stackoverflow has a great Post explaining its use and its advantages.
Explaining in a general way it allows to separate the database into several areas, making it possible for a bank to be separated 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 it Is common that there are columns in our banks that do not contain only primitive data such as string and int. Product Descriptions 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 ecommerce data
- Process log and telemetry data
- Storing semi-structured data from IoT
- Simplify the development of the REST API
3. Remember the normal shapes
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, the Stackoverflow also has an excellent explanation of anomalies that can occur when we forget it. Following up to the third normal form already assists a lot in organizing 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 as if , else and While
- Remember to use Try , Catch , Commit and Roolback to prevent errors
- Use Prints to record which condition structures are being executed.
- Comment Your code the most Possible
[Conclusion]: 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 a bank.