从数据库中检查3表，同时保存每个表的信息以进行身份​​验证登录表单php [关闭]

Question

I have tables like this

Employee

Id | IdEmployee  | Employee | Address     | Phone_num | 
1  | EM10000     |  Jack    | wall st.    | 9000010   | 
2  | EM10001     |  Paul    | elm st.     | 9000010   |
3  | EM10002     |  George  | ex  st .    | 9000010   |

Technician

id | IdTech      | TechName | Address      | phone_num | 
1  | TE10000     | Gabut    | Hello st.    | 9000010   | 
2  | TE10001     | Polnaref | coding st.   | 9000010   |
3  | TE10002     | Rafioli  | stack st.    | 9000010   |

Admin

id | IdAdmin     | username | password     | phone_num | 
1  | AM10000     |  sim1    | 1234         | 9000010   | 
2  | AM10001     |  sim2    | luvcoding    | 9000010   |
3  | AM10002     |  sim3    | okaymate     | 9000010   |

How do i make Table Technician and Employee become a user too, so they could use their information as username in a login form? Plus, i want each Id (not the "AutoIncrement" ID) of Technician and Employee would become the username.

How to manage the database like this?

or should i add password column in both tables? (Employee and Technician)

or should i make one table just like this?

(new) Admin

id | Id          | username | password     | phone_num | 
1  | AM10000     |  sim1    | 1234         | 9000010   | 
2  | AM10001     |  sim2    | luvcoding    | 9000010   |
3  | AM10002     |  sim3    | okaymate     | 9000010   |
4  | AM10003     |  EM10000 | test1        |  NULL     |
5  | AM10004     |  EM10001 | test2        |  NULL     |
6  | AM10005     | EM10002  | test3        |  NULL     |
7  | AM10006     | TE10000  | 1234         |  NULL     | 
8  | AM10007     | TE10001  | ok123        |  NULL     |

There is no relationship between Technician and Employee (stand-alone table)

Answer 1

In your case, you would be better making your tables in normalization form as follows:

Employees

IdEmployee  | Employee | Address     | Phone_num 
EM10000     |  Jack    | wall st.    | 9000010  
EM10001     |  Paul    | elm st.     | 9000010  
EM10002     |  George  | ex  st .    | 9000010 

Technicians

IdEmployee | TechName 
TE10000    | Gabut   
TE10001    | Polnaref

Admins

IdEmployee  |  Password  
TE10001     |  hashed_pass1 
EM10002     |  hashed_pass2 

This makes sure you have no replicated data and ensures you can associate tables correctly by using foreign keys.

So if your admin has IdAdmin 'EM10002', to get the password of that employee with the following query:

// Will return a row with EM10002's password, which is 'hashed_pass2'. 
SELECT Password from Admins WHERE IdEmployee = EM10002

Here is another example of foreign key usage.

To understand normalization more, have a look at this thread.