In the realm of web development, building applications that are scalable, secure, and maintainable is crucial. One key aspect of achieving this is through effective database modelling and design. In this article, we'll explore how to design a relational database schema for a Role-Based Access Control (RBAC) system using Python Flask, SQLAlchemy, and Flask-Migrate.


Step 1: Entity Relationship with attributes

Before diving into coding, it's essential to understand the entities and their relationships within the system. Here are the entities identified for our RBAC system along with their attributes:

- Roles: role_id, role_name, created_at, updated_at
- Users: user_id, role_id(fk), username, email_address, password, lastname, firstname, phonenumber, dateofbirth, created_at, updated_at
- Modules: module_id, module_title, module_order, module_icon, created_at, updated_at
- Tasks: task_id, module_id(fk), task_label, task_url, task_route, task_method, task-icon, isdashboard, isnavbar, created_at, updated_at
- Privileges: privilege_id, task_id(fk), role_id(fk), created_at, updated_at

Step 2: SQLAlchemy Models

To interact with the database, we'll use SQLAlchemy, a powerful SQL toolkit and Object-Relational Mapping (ORM) library for Python. Here's how to set up SQLAlchemy:

```python
# Install SQLAlchemy
pip install Flask SQLAlchemy
```

Set up the database configuration in `flaskenv.py`:

```python
DATABASE_URI='mysql://root:root@localhost/python_rbac_db?unix_socket=/Applications/MAMP/tmp/mysql/mysql.sock'
```

Connect to the MySQL database in `config.py`:

```python
SQLALCHEMY_DATABASE_URI = os.environ.get('DATABASE_URI')
```

Create `mydb.py` and import SQLAlchemy:

```python
from flask_sqlalchemy import SQLAlchemy
db = SQLAlchemy()
```

Step 3: Create Models

Based on the identified RBAC entities and attributes, we'll create SQLAlchemy models. For instance:

```python
from mydb import db

class RoleModel(db.Model):
__tablename__ = 'roles'
role_id = db.Column(db.Integer, primary_key=True)
role_name = db.Column(db.String(50), nullable=False)
created_at = db.Column(db.DateTime, nullable=False)
updated_at = db.Column(db.DateTime, nullable=False)
# Define relationships with other tables if needed

class UserModel(db.Model):
__tablename__ = 'users'
user_id = db.Column(db.Integer, primary_key=True)
# Define other columns
```

Update `__init__.py` for Database Migrations:

```python
from mydb import db
from flask_migrate import Migrate

migrate = Migrate()
migrate.init_app(app, db)
```

Step 4: Database Migrations

Database migrations are essential for managing changes to the database schema over time. We'll use Flask-Migrate, which is a Flask wrapper for Alembic.

Install Flask-Migrate and mysqlclient:

```bash
pip install flask-migrate
pip install mysqlclient
```

Create Migration Repository:

```bash
flask db init
```

Generate Migration Files:

```bash
flask db migrate
```

Run Migration:

```bash
flask db upgrade
```

By following these steps, we've successfully designed our database schema for the RBAC system using Python Flask, SQLAlchemy, and Flask-Migrate. This ensures that our application remains flexible and maintainable as it evolves over time.

In the next stages of development, we can focus on implementing business logic, authentication, authorization, and other functionalities to complete our RBAC system.

Building robust applications requires attention to detail, especially in areas like database modelling and design. With the tools and techniques outlined in this article, developers can create scalable and secure web applications with ease.

Source Code on GitHub
Click here to read more and download the source code. https://academy.strideshub.com/blogdetails/day-4-building-a-python-flask-application-with-database-modelling-and-design/Nw==