JS Pinto JS Pinto - 1 month ago 7
MySQL Question

Different types of users redirected to same page (index.php) with different content

I making a PHP class to login the users, based on EMAIL and PASSWORD. I want them to go to the same page (index.php) on login, but showing different content.
I saw other ACL questions but most of them regarding user escalation, and different group roles.
Here the roles are pretended to be static.

I have 3 SQL Tables:



CREATE TABLE `USERS` (
`userID` int(10) NOT NULL PK AUTOINCREMENT,
`groupID` int(10) NOT NULL,
`EMAIL` varchar(99) NOT NULL,
`PASSWORD` text NOT NULL,
`regDate` date NOT NULL
)

CREATE TABLE `SUPPORT` (
`ID` int(10) NOT NULL PK AUTOINCREMENT,
`ENGINEERNUMBER` int(10) NOT NULL,
`EMAIL` varchar(99) NOT NULL,
`PASSWORD` text NOT NULL,
`NAME` varchar(99) NOT NULL,
`OFFICE` varchar(99) NOT NULL
)

CREATE TABLE `CUSTOMER` (
`ID` int(10) NOT NULL PK AUTOINCREMENT,
`CITIZENID` int(10) NOT NULL,
`EMAIL` varchar(99) NOT NULL,
`PASSWORD` text NOT NULL,
`NAME` varchar(99) NOT NULL,
`CITY` varchar(99) NOT NULL,
`ADDRESS`varchar(99)
)


ALTER TABLE `USERS`
ADD CONSTRAINT `USERS_ibfk_1` FOREIGN KEY (`EMAIL`) REFERENCES `SUPPORT` (`EMAIL`),
ADD CONSTRAINT `USERS_ibfk_2` FOREIGN KEY (`EMAIL`) REFERENCES `CUSTOMER` (`EMAIL`),


**My DB schema is good?
Should i have a PHP file, with and if statement, filling index.php with HTML, based on user role?

Thanks in advance, sorry for long post.**

UPDATE

CREATE TABLE `USERS` (
`ID` int(10) NOT NULL PK AUTOINCREMENT,
`NAME` varchar(99) NOT NULL,
`EMAIL` varchar(99) NOT NULL,
`PASSWORD` text NOT NULL,
)

CREATE TABLE `TYPES` (
`ID` int(10) NOT NULL PK AUTOINCREMENT,
`NAME` int(10) NOT NULL,
)

CREATE TABLE `SUPPORT` (
`ID` int(10) NOT NULL PK AUTOINCREMENT,
`USERID` int(10) NOT NULL ,
`ENGINEERNUMBER` int(10) NOT NULL,
`OFFICE` varchar(99) NOT NULL
)

CREATE TABLE `CUSTOMER` (
`ID` int(10) NOT NULL PK AUTOINCREMENT,
`USERID` int(10) NOT NULL,
`CITIZENID` int(10) NOT NULL,
`CITY` varchar(99) NOT NULL,
`ADDRESS`varchar(99)
)


ALTER TABLE `CUSTOMER`
ADD CONSTRAINT `CUSTOMER_ibfk_1` FOREIGN KEY (`USERID`) REFERENCES `USERS` (`ID`)

ALTER TABLE `SUPPORT`
ADD CONSTRAINT `SUPPORT_ibfk_1` FOREIGN KEY (`USERID`) REFERENCES `USERS` (`ID`)

ALTER TABLE `TYPES`
ADD CONSTRAINT `TYPES_ibfk_1` FOREIGN KEY (`ID`) REFERENCES `USERS` (`TYPEID`)


REVISED DB SCHEMA

Answer

I would not recommend filling the index.php with ifs and html but rather using ifs and include of other html snippets.

As for your DB schema you are on the right path but I would change the following.

Leave name, password and email only on the USER table. These are common across users, my guess, and you don't want to deal with updating data in multiple places especially for something like password.

You will want to add a TYPE or TYPE table with an ID and NAME column (make name the same as the table name) and then add a TYPEID to USER with an FK. This way you now know a user is of type SUPPORT or CUSTOMER and can then build a query to find the other info (see below).

Add a column to SUPPORT and CUSTOMER for USERID and make this your FK to USER ID. The advantage here is that now users can change emails and you know that the USERID is unique since it is PK of the USER table.

You can then query for information like this:

//There is an assumption of object here with $user->type.
$query = "SELECT * FROM USER u JOIN {$user->type} t ON u.TYPEID = t.ID WHERE u.ID = ?";

As well in your index show the includes like:

if($user->type == "SUPPORT"){
    include('path/to/support/template.php');
} elseif($user->type == "CUSTOMER"){
    include('path/to/customer/template.php');
} else {
    //Default catch bad types?
}

UPDATE TO SCHEMA

CREATE TABLE `USERS` (
    `ID` int(10) NOT NULL PK AUTOINCREMENT,
    `TYPEID` int(10) NOT NULL,
    `NAME` varchar(99) NOT NULL,
    `EMAIL` varchar(99) NOT NULL,
    `PASSWORD` text NOT NULL,
)

ALTER TABLE `USERS`
    ADD CONSTRAINT `USERS_ibfk_1` FOREIGN KEY (`TYPEID`) REFERENCES `TYPES` (`ID`)