A database design problem, one-to-many relationship, how to design a table?

freeboy99

There are first-level departments and sub-departments in the enterprise

For example, there is a planning center and a research and development center, which belong to the first-level department.
The planning center includes the design department, the planning department, and the editorial department.
The R & D center includes the Delphi R & D department and the Java R & D department.

What to do when designing the department information sheet?
I currently design it like this
A total of two tables
One table is the first-level department information table
department1:
  First Department Number
  First-level department name

department2:
  First Department Number
  Secondary department number
  Secondary department name

Because some departments have no sub-secondary departments.

Do you think there is something wrong with the design above?
A first-level department can have multiple second-level departments or no second-level departments
A secondary department corresponds to only one primary department

In the actual situation of the enterprise, shouldn't there be two identical second-level department names under different first-level departments?

How to optimize my database design above? Or better suggestions ???
Thank you..

unkmass

A table should be fine.

unkmass

For example:
create table dept (
code int not null,
dep_code int null,
name char (50) not null,
constraint PK_DEPT primary key (code)
)
go

alter table dept
   add constraint FK_DEPT_RELATIONS_DEPT foreign key (dep_code)
      references dept (code)
go

2227368wfs

This kind of problem is done with the parent-child table relationship

One is the department code table

One is the job code table. This table should contain the department where the job is located.

2227368wfs

One is the department code table (dept_no, dept_name), and the primary key is dept_no

One is the position code table (position_no, position_name, dept_no), the primary key is position_no

The structure of these two tables is very important to the structure of the entire company

fanfan1980

Agree with the first floor

imapiggy

In the actual situation of the enterprise, shouldn't there be two identical second-level department names under different first-level departments?

There will be two identical secondary departments, but only the same name, the number is different!

wtyrambo

create table #department
(
dep_no numeric primary key,
dep_name varchar (20),
father_dep_no numeric references #department (dep_no)
)

insert into #department values ​​(1, 'Plan Center', null)
insert into #department values ​​(2, 'R & D Center', null)
insert into #department values ​​(3, 'Design Department', 1)
insert into #department values ​​(4, 'Planning Department', 1)
insert into #department values ​​(5, 'Editor', 1)
insert into #department values ​​(6, 'delphi R & D', 2)
insert into #department values ​​(7, 'java R & D', 2)

freeboy99

Not quite sure whatunkmassmeans. .

Can you write it in relational mode? ?

freeboy99

Because I am not involved in employee positions here. .

Only the department involved