Home / CMS


    Topnew CMS - User Database

    2022-06-05 08:32
    712   0   0

    Today we are going to make a user system, which will be used for login, user management, role permission, page access control etc.

    1 - team

    CREATE TABLE team (
    id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    id_up int NOT NULL DEFAULT 0, -- parent team id
    name varchar(99) NOT NULL DEFAULT '',
    info varchar(255) NOT NULL DEFAULT '',
    UNIQUE KEY team_uk_name (name)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

    Other name for this table can be company, organisation, unit, faculty etc.

    2 - user

    This table is used to save all users for login purpose. It can be mixed of staff and customers. Common fields will be:

    CREATE TABLE user (
    id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name varchar(99) NOT NULL DEFAULT '',
    email varchar(99) NOT NULL DEFAULT '',
    created timestamp NOT NULL DEFAULT now(),
    closed date NOT NULL DEFAULT '1970-01-01',
    team_id int NOT NULL DEFAULT 0,
    sid int NOT NULL DEFAULT 0,
    error smallint NOT NULL DEFAULT 0,
    pass varchar(255) NOT NULL DEFAULT '',
    UNIQUE KEY user_uk_email (email),
    CONSTRAINT user_fk_team FOREIGN KEY (team_id) REFERENCES team (id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

    Other common fields might be: gender, dob, address, suburb, state, postcode, etc

    3 - role

    CREATE TABLE role (
    id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name varchar(99) NOT NULL DEFAULT '',
    info varchar(255) NOT NULL DEFAULT '',
    UNIQUE KEY role_uk_name (name)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

    4 - task

    CREATE TABLE task (
    id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name varchar(99) NOT NULL DEFAULT '',
    info varchar(255) NOT NULL DEFAULT '',
    UNIQUE KEY task_uk_name (name)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

    Other name for this table can be permission, access etc

    5 - user_role

    CREATE TABLE user_role (
    user_id int NOT NULL DEFAULT 0,
    role_id int NOT NULL DEFAULT 0,
    PRIMARY KEY (user_id,role_id),
    CONSTRAINT user_role_fk_role FOREIGN KEY (role_id) REFERENCES role (id),
    CONSTRAINT user_role_fk_user FOREIGN KEY (user_id) REFERENCES user (id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

    6 - role_task

    CREATE TABLE role_task (
    role_id int NOT NULL DEFAULT 0,
    task_id int NOT NULL DEFAULT 0,
    PRIMARY KEY (role_id,task_id),
    CONSTRAINT role_task_fk_role FOREIGN KEY (role_id) REFERENCES role (id),
    CONSTRAINT role_task_fk_task FOREIGN KEY (task_id) REFERENCES task (id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

    Also try to go through all business logic, and check if the SQL query performance works well, and make adjustment if needed.
    Back « Topnew CSS v 2023 JS related

    Comments

    Leave a commentEdit comment

    Category