What are some best practices for structuring PHP and SQL tables for a user login system with different roles?

When creating a user login system with different roles in PHP and SQL, it is important to have a clear and structured database design. One common approach is to have a 'users' table with fields such as 'id', 'username', 'password', and 'role_id'. Additionally, a 'roles' table can be created to define different roles with corresponding permissions. Using a role-based access control system allows for easier management and scalability of user roles.

// Create users table
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    password VARCHAR(255) NOT NULL,
    role_id INT NOT NULL,
    FOREIGN KEY (role_id) REFERENCES roles(id)
);

// Create roles table
CREATE TABLE roles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL
);

// Insert roles data
INSERT INTO roles (name) VALUES ('admin');
INSERT INTO roles (name) VALUES ('user');