Read about normalisation and abount foreign keys, eg ...
https://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch01.pdf
https://www.artfulsoftware.com/dbdesignbasics.html
Most simply it could look something like this ...
departments(
deptid unsigned primary key auto_increment,
deptname varchar(128) not null,
...
)
employees(
empid int unsigned primary key auto_increment,
lastname varchar(64),
firstname varchar(64),
deptid unsigned int,
foreign key(deptid) references departments(deptid)
on delete cascade on update cascade,
...
)
Or if you wish to preserve history eg when an employee shifts dept, lose the employees foreign key and instead ...
positions(
empid int unsigned,
deptid int unsigned,
datestart date,
datestop end,
position_name varchar(64),
primary key(empid,deptid,datestart),
foreign key(empid) references employees(empid)
on delete cascade on update cascade,
foreign key(deptid) references(departments)
on delete cascade on update cascade
)
Edited 1 time(s). Last edit at 06/04/2022 11:06AM by Peter Brawley.