mySQL表结构添加新用户表[关闭]

I have a small app in php using the cakePHP framework connected to mySql database.

I have two types of users (business and employees) with relationships to other tables also. I am now adding a login facility which means I will be adding a users table to hold the password details. So both employees and businesses will be able to login.

I am not sure what way to add the relationships between the users table and the businesses and employees table. I will be adding an admin role also.

My current tables are: - course_files - course_modules - courses - courses_employees - employees - businesses

My Choices:

1. Merge the businesses and employees table with the users and have a
   • users (id, role employee_name, business_name, business_address, etc)
   • roles (admin, employee, business)

My issue with this approach is that the fields for businesses and employees are very different so the users table will have a lot of fields. But it will make it easier for the login functionality.

2. Add business_id and employee_id to the users table. This option will be a little more involved and one field will always be blank like employee_id.

   • users (id, employee_id, business_id, role)
   • roles (admin, employee, business)
   • employees (business_id)
   • businesses

So before I go down one route and find I went in the wrong direction I was wondering what would be best practice?

I disagree with both of your approaches. I would have a users table for login, and tables for employees and business, just like you suggested in your second approach. The difference is that I wouldn't have employee_id and business_id in users because, as you've already stated, one will always be blank. Why don't you instead have a user_id in both tables, as they will always have a user profile (intended for login)? I see you use CakePHP, so these will be relationships as follows:

User hasOne Employee
User hasOne Business
Employee belongsTo User
Business belongsTo User

Let me know if any other business rules in your application make my approach hard to implement.