Database refactoring

As discussed in the preceding section, our application database is huge and depends on a single schema. This huge database should be considered while refactoring. We will go for this as:

• Schema correction: In general practice (not required), our schema depicts our modules. As discussed in previous sections, our huge database has a single schema, that is dbo now, and every part of the code or table should not be related to dbo. There might be several modules that will interact with specific tables. For example, our Order module should contain some related schema name, such as Order. So whenever we need to use the table, we can use them with their own schema instead of a general dbo schema. This will not impact any functionality related to how data would be retrieved from the database. But it will have structured or arranged our tables in such a way that we would be able to identify and correlate each and every table with their specific modules. This exercise will be very helpful while we are in the stage of transitioning a monolithic application to microservices. Refer to the following image:

In the preceding figure, we see how the database schema is separated logically. It is not separated physically—our Order Schema and Stock Schema belong to the same database. So here we separate the database schema logically, not physically.

We can also take an example of our users—not all users are admin or belong to a specific zone, area, or region. But our user table should be structured in such a way that we should be able to identify the users by the table name or the way they are structured. Here we can structure our user table on the basis of regions. We should map our user table to a region table in such a way it should not impact or lay any changes in the existing code base.

• Moving business logic to code from stored procedures: In the current database, we have thousands of lines of Stored Procedure with a lot of business logic. We should move the business logic to our codebase. In our monolithic application, we are using Entity Framework; here we can avoid the creation of stored procedures. We can incorporate all of our business logic to code.