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==

I'll Go And Talk To The President

I'll go to the president and tell him about the people's plight,
I'll go to the president and tell him about the ambushed and killing of farmers,
I'll go to the president and talk to him about the herdsmen and their settlement,

I'll go to the president and remind him about his unfulfilled promises,
I'll go to the president and tell him about the masses hardship, agony, anguished and plea.

I'll go to the president and remind him about the enormous natural resources the country is blessed with,

I'll go to the politicians and remind them of yesterday promises not tomorrow's election,

I'll go to the house of representatives to remind them of people's plea to represent them well..

I'll go to the house of corrupt leaders and tell them about the Messiah who gives them power to govern in truth not to be the ambassadors of corruption..

I'll go to the universe and beyond and tell all about the greed of men in Power,

I'll go to the president and tell him about the high rate of unemployment, crimes, segregation, tribalism, favouritism, religious differences, nepotism, regionalism and people's clamour for change..
I'll go and tell him these and more..

---

By Ezra Bature

https://naijapoetry.com/Home/Poem?poemid=684#tagpoem_684

School Management Software, University Portal, College Portal. Contact africpoet@yahoo.com