Issues with our model definition

While SQLAlchemy provided us with a lot of abstraction to easily define our user model, it also makes it easy for us to make some mistakes, which can prove to be costly once the application use scales up and the enterprise grows. Let's take a look at some of the mistakes that we have made while defining this model:

• Vulnerability to changes: The current definition of our user model makes it very hard to make changes to the model once the application scales up. Let's take the example of the organization deciding to provide users with more permissions on a bug report. In terms of SQL, to achieve the effect, we will need to write a query that will traverse through all the records and has user_role as the user and update the user_role_permissions column for them. This would be absolutely fine if we had only a few hundred users, but the problem would start to creep up on us if the database contains the records of thousands of users in an organization. We would immediately start to see our application slow down because updates would take a long time. This problem will be amplified if we had to modify the permissions of more than one user_role.
• High repetitiveness: In our model, we have to provide the values for user_role and user_role_permissions for every other record that we insert. This creates a lot of repetitive storage of data and unnecessarily increases the storage size of the records in our database. This kind of mistake doesn't show up early in the application's use, but shows up when the application grows in use. All of a sudden, you will see issues such as spikes in memory and increased disk usage.
• Prone to inconsistencies: As humans, no matter what we do, we cannot completely eliminate the possibility of making errors. If the same error happens in the logic that feeds in the data to our user model, and is detected late, it doesn't take a lot of guesswork to estimate that a lot of the records in our user table would then have inconsistent data. For example, in a particular code commit, we accidentally renamed the administrator role  admin; now we have a huge amount of records that have administrator privileges but with two different role names, which we now need to support in our application.

Now we know about the issues our current model causes us, it's time to take a look at how to build models that are not prone to these issues, and can scale easily as our application expands.