Project Database Design

We can create a database named:

student_management

This database will contain the tables required by our mobile application and REST API.

CREATE DATABASE student_management;

After creating the database, select it before creating tables.

USE student_management;

Our project will initially use two main tables:

student_management
│
├── users
│
└── students

• users stores application users.
• students stores student records.

The users table will store the information required for registration and login.

users

id
name
email
password
role
created_at

The password field will contain a secure password hash rather than the original password.

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    role VARCHAR(30) NOT NULL DEFAULT 'user',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

The email is unique so that two users cannot register using the same email address.

The id column uniquely identifies each user.

id INT AUTO_INCREMENT PRIMARY KEY

For example:

Email will be used as the login identifier.

email VARCHAR(150) NOT NULL UNIQUE

The UNIQUE constraint prevents duplicate email addresses.

Never store a user's plain password in the database.

PHP can create a password hash using:

$hash = password_hash(
    $password,
    PASSWORD_DEFAULT
);

The generated hash is stored in the password column.

The role field can be used for authorization.

role

Example values:

admin
user

Later, the API can check the user's role before allowing administrative operations.

The students table will store the student information managed by the mobile application.

students

id
name
email
mobile
course
address
created_at

CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150),
    mobile VARCHAR(20),
    course VARCHAR(100),
    address TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

The id column uniquely identifies each student.

id INT AUTO_INCREMENT PRIMARY KEY

When a new student is inserted, MySQL automatically generates the next ID.

The student's name is required.

name VARCHAR(100) NOT NULL

The PHP API should also validate the name before inserting it into the database.

The student's email can be stored in the email column.

email VARCHAR(150)

The API can validate the email format before saving it.

A mobile number can be stored using a VARCHAR field.

mobile VARCHAR(20)

Phone numbers should generally be stored as text because they are identifiers rather than mathematical values.

The course field stores the course or program in which the student is enrolled.

course VARCHAR(100)

Example:

ADCA
Java
Python
Web Development
React Native

The student's address can contain a longer text value.

address TEXT

The TEXT type is useful when the address can contain more characters than a short VARCHAR field.

The created_at field records when the record was created.

created_at TIMESTAMP
DEFAULT CURRENT_TIMESTAMP

The application does not need to send this value manually when creating a record.

The users table is used for application authentication, while the students table contains student records.

users
  |
  | Authentication
  |
React Native
  |
  | JWT
  ↓
students API
  |
  ↓
students table

The authenticated user does not directly connect to MySQL. All access goes through the API.

PHP will connect to MySQL using PDO.

$pdo = new PDO(
    "mysql:host=localhost;dbname=student_management",
    "root",
    ""
);

In a real production environment, database credentials should be protected and should not be exposed in the mobile application.

Student queries should use prepared statements.

$stmt = $pdo->prepare(
    "SELECT * FROM students
     WHERE id = ?"
);

$stmt->execute([$id]);

Prepared statements help protect the API against SQL injection.

React Native
      ↓
Axios Request
      ↓
PHP REST API
      ↓
Validate Request
      ↓
PDO
      ↓
MySQL
      ↓
JSON Response
      ↓
React Native

This separation keeps the mobile application independent from the database.

The students table will support the four basic CRUD operations.

The API will later support student searching.

SELECT *
FROM students
WHERE name LIKE ?
   OR email LIKE ?
   OR mobile LIKE ?;

Values should be supplied through prepared statement parameters.

Pagination can be implemented using LIMIT and OFFSET.

SELECT *
FROM students
ORDER BY id DESC
LIMIT ? OFFSET ?;

The API can calculate the offset using:

$offset = ($page - 1) * $limit;

student_management
│
├── users
│   ├── id
│   ├── name
│   ├── email
│   ├── password
│   ├── role
│   └── created_at
│
└── students
    ├── id
    ├── name
    ├── email
    ├── mobile
    ├── course
    ├── address
    └── created_at

• Never store plain passwords.
• Use password_hash().
• Use password_verify() during login.
• Use PDO prepared statements.
• Validate API input.
• Protect database credentials.
• Do not expose MySQL directly to React Native.
• Use JWT authentication for protected APIs.
• Use HTTPS in production.

CREATE DATABASE student_management;

USE student_management;

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    role VARCHAR(30) NOT NULL DEFAULT 'user',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150),
    mobile VARCHAR(20),
    course VARCHAR(100),
    address TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

This creates the basic database structure required for our project.

MySQL Database
      ↓
PDO Connection
      ↓
PHP REST API
      ↓
JWT Authentication
      ↓
JSON Response
      ↓
Axios
      ↓
TypeScript
      ↓
React Native Screens

Our database is now ready for the next stage of the project. In the following lessons, we will create the registration API, login API, JWT authentication, and student management APIs step by step.