Optimizing our models

Before we discuss how to build optimal models, we first need to understand the characteristics that need to be present in an optimal model. Let's take a look at the following:

• Easy to adapt: An optimal model should be easy to adapt according to the changing needs of the application as its user base grows. This means changing a particular model should not require changes all across the application, and should be high in cohesion.
• Maximizes the throughput on a host: Every host has a different architecture, and a data model should be able to exploit the underlying host resources in a manner that maximizes its throughput. This can be made possible by using the correct data storage engine for a particular architecture and use case, or running the database across a cluster of machines to increase the parallel execution capabilities.
• Efficient storage: A database model should also be considerate of the storage it may use as the data being stored inside it grows. This can be done by carefully choosing data types. For example, just to represent a column that can have only two values, true or false, an integer type would be overkill, wasting a lot of disk space, as the number of records in the database grows. A nominal data type for such a column could be Boolean, which doesn't takes that much space internally.
• Easy to tune: An efficient model will carefully index the columns that can speed up the processing of queries against a particular table. This results in an improved response time for the database, and having happy users who don't get frustrated because your application takes up to 20 minutes to return 10,000 records from the database.

To achieve these goals, we now need to simplify our models, and use the concept of relationships that relational databases provide. Let's now start re-factoring our user model to make it a bit more optimal.

To achieve this, first we need to break it down from one large model to multiple small models, which live independently in our code base and don't have everything coupled so hard. Let's get started.

The first thing that we will move out of the model is how we deal with roles and permissions. Since roles and their permissions are not something that will differ too much from user to user (for sure not every user will have a unique role, and not every role can have a varying set of permissions), we can move these fields to a different model, known as permissions. The following code illustrates this:

class Role(Base):
 __tablename__ = 'roles'

 id = Column(Integer, primary_key=True, autoincrement=True)
 role_name = Column(String(length=25), nullable=False, unique=True)
 role_permissions = Column(Integer, nullable=False)

 def __repr__(self):
 return "<Role {}>".format(role_name)

Now, we have the roles decoupled from the user model. This makes it easy for us to make a modification to the provided roles without causing much of an issue. These modifications may include renaming a role or changing the permissions for an existing role. All we do is make a modification in a single place and it can be reflected for all the users that have the same role. Let's see how we can do this with the help of relations in Relational Database Management System (RDBMS) in our user model.

The following code example shows how to achieve the relation between the role model and the user model:

class User(Base):
  __tablename__ = 'users'

  id = Column(Integer, primary_key=True, autoincrement=True)
  first_name = Column(String, nullable=False)
  last_name = Column(String, nullable=False)
  username = Column(String(length=25), unique=True, nullable=False)
  email = Column(String(length=255), unique=True, nullable=False)
  password = Column(String(length=255), nullable=False)
  date_joined = Column(Date, default=datetime.now())
  user_role = Column(Integer, ForeignKey("roles.id"))
  account_active = Column(Boolean, default=False)
  activation_key = Column(String(length=32))

  def __repr__(self):
    return "<User {}>".format(self.username) 

In this code example, we modified the user_role to be an integer, and stored a value that is present in the roles model. Any attempt to insert a value into this field that is not present in the roles model will raise an SQL exception that the operation is not permitted.

Now, continuing with the same example, let's think about the activation_key column of the user model. We might not need an activation key once the user has activated their account. This provides us with an opportunity to perform one more optimization in our user model. We can move this activation key out of the user model and store it in a separate model. Once the user has successfully activated their account, the record can be safely deleted without the risk of the user model being modified. So, let's develop the model for the activation key. The following code sample illustrates what we want to do:

class ActivationKey(Base):
  __tablename__ = 'activation_keys'

  id = Column(Integer, primary_key=True, autoincrement=True)
  user_id = Column(Integer, ForeignKey("users.id"))
  activation_key = Column(String(length=32), nullable=False)

  def __repr__(self):
    return "<ActivationKey {}>".format(self.id)

In this example, we implemented the ActivationKey model. Since every activation key belongs to a unique user, we need to store which user has which activation key. We achieve this by introducing a foreign key to the user model's id field.

Now, we can safely remove the activation_key column from our user model without causing any trouble.