The database model forms an integral part of any real life application. This is because any serious application in enterprises will for sure be dealing with some kind of data that needs to be persisted across the time.

The same is the case for our BugZot. BugZot is used to track the bugs and their life cycle that are encountered in the products of Omega Corporation. Also, the application will have to keep a record of users that are registered on it. To achieve this, we will require multiple models, each serving its own purpose.

For the development of this application, we will group all the related models under their own separate directories, so that we can maintain the clarity about which models serves what purpose. Also, this allows us to keep the code base clean from clutter, which may make it hard for the developers to understand what each file does as the code base grows in the future.

So, let's first get started with the development of the models that are required to manage the user accounts-related information.

To get started with the development of the user account-related models, we first go on and create a directory named users inside our models directory:

mkdir bugzot/models/users

Then initialize it as a submodule inside the models module.

Once we are done with this, we are good to go with the creation of our user model whose definition is shown in the following code:

'''
File: users.py
Description: The file contains the definition for the user data model
             that will be used to store the information related to the
             user accounts.
'''
from bugzot.application import db
from .roles import Role


class User(db.Model):
    """User data model for storing user account information.

    The model is responsible for storing the account information on a
    per user basis and providing access to it for authentication
    purposes.
    """

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    username = db.Column(db.String(50), unique=True, index=True, nullable=False)
    password = db.Column(db.String(512), nullable=False)
    email = db.Column(db.String(255), unique=True, nullable=False, index=True)
    user_role = db.Column(db.Integer, db.ForeignKey(Role.id))
    role = db.relationship("Role", lazy=False)
    joining_date = db.Column(db.DateTime, nullable=False)
    last_login = db.Column(db.DateTime, nullable=False)
    account_status= db.Column(db.Boolean, nullable=False, default=False)

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

With this, we just created our user model that can be used to store the information related to our users. Most of the columns just provide the definitions for the data that we expect to store inside the database. However, there are a couple of interesting bits here, so let's go through them:

index=True

We can see this attribute is mentioned in the username and the email column definition. We set the index attribute to True in the two columns because these two columns can be used frequently to access the data related to a particular user, and hence can benefit from the added optimization that comes with indexing.

The next interesting piece of information here is the relationship mapping to the roles model.

role = db.relationship("Role", lazy=False)

Since every user inside our database has a role associated to it, we can just add a one to one relationship mapping from our user model to the role model. Also, if we take a look carefully, we have set lazy=False. There is a small reason why we want to avoid lazy loading here. The roles model is usually small, and there is only a one-to-one mapping from the users model to the role model. By avoiding lazy loading, we are shedding off some time that would have been spent in waiting, had our database access layer lazily loaded the data from the roles model. Now, the question arises, where is the role model?

The definition of the role model can be found under the bugzot/models/users/roles.py file, but we explicitly are not providing that definition here inside the book, to keep the chapter concise.

Also, we need a mechanism for verifying the email address of the users. We can do this by sending the users a small email containing an activation link, which they need to click. To do this, we also have to generate and store an activation key for every new user. For this, we leverage a new model named ActivationKey model whose definition can be found under the bugzot/models/users/activation_key.py file.

Once all of this is done, we are now ready to export these models out of our users model submodule. To do this, let's fire up the module entrypoint file inside our code editor and export the models by adding the following lines to the bugzot/models/users/__init__.py file:

from .activation_key import ActivationKey
from .roles import Role
from .users import User

With this, we are done with the definition of our data models related to storing the information about the users.

The next thing inside our application is to define data models related to the categorization of the products for which the bugs can be filed. So, let's jump into the creation of the models related to the product categorization.

For creating the models related to the products, we first create a new submodule directory under the bugzot/models module and initialize it. Next, we provide the definition for the product model under bugzot/models/products/products.py as shown in the following code:

'''
File: products.py
Description: The file contains the definition for the products
             that are supported for bug filing inside the bug tracker
'''
from bugzot.application import db
from .categories import Category


class Product(db.Model):
    """Product defintion model.

    The model is used to store the information related to the products
    for which the users can file a bug.
    """

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    product_name = db.Column(db.String(100), nullable=False, unique=True, index=True)
    category_id = db.Column(db.Integer, db.ForeignKey(Category.id))
    category = db.relationship("Category", lazy=True)

    def __repr__(self):
        """Product model representation."""
        return "<Product {}>".format(self.product_name)

With this a we have completed the definition of the product model that will be used to keep a track of the products, against which the bugs can be filed in our application.

There are a few more model definitions inside our products submodule as follows:

• Category: The category model is responsible for storing the information about the product categories to which a particular product belongs
• Component: The component model is responsible for storing the information related to the product components against which a bug can be filed
• Version: The version model is responsible for storing the information related to the product versions against which a bug can be categorised

Once all these models are defined, they can be exported out from the product's submodule so that they can be utilized inside the application.

In a similar manner, we define the models related to the tracking of the bugs inside the system. We will skip over mentioning the definition of these models inside this chapter to keep the chapter length reasonable, but, for the curious mind, the definitions of these models can be easily tracked inside the bugzot/models/bugs directory in the code repository for this chapter.