mysql - 我这样的表结构如何一个用户关联多个公司多个部门多个岗位？

Answer 1

Company table comapny

公司ID	公司名
1	A
2	B

Department table department

部门ID	部门名
1	X
2	Y
3	Z

User table user

用户ID	用户名	其它字段...
1	王二	etc...
2	张三	etc...
3	李四	etc...

User company department relationship table user_company_department_relation

关联自增ID	用户ID	公司ID	部门ID
1	1	1	3
2	1	2	2
3	2	1	1

Answer 2

Hello lz, my personal idea is this. Put all companies in a table and add a mark to indicate which company they belong to. Put all departments in a table and add a mark to indicate which company they belong to.
If the next level is the position table (not specified in the original poster's question), then add a mark to store the department id in the department table.
If the next level is the user table, then the json data of the ID of the user storage department is converted into a string and stored in a column.

When you are in a hurry, your thinking is not careful. If there is anything unreasonable, please point it out

Answer 3

Generally I will build the table like this
companies company table

company_id primary key, auto-increment
company_name company name

departments department table

department_id department id primary key, auto-increment
department_name department name
company_id foreign key, related to the company table

jobs job list

job_id job id, primary key, auto-increment
job_name job name
department_id department id

= . =In this way, the relationship will be established layer by layer. .
There are not many write statuses in these tables, but many read statuses. If you want to count the number of positions and departments, you can open a table such as company_status

Answer 4

Add the foreign key of the company table to the department table