I'm currently working on a design for an appraisal system.

Here is my current design:

create table users (
id int not null auto_increment primary key,
username varchar(20) NOT NULL
);

insert into users (username)
values ('John'),('Peter'),('Harry'),('Sarah');

create table goals (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
title varchar(45) NOT NULL
);

insert into goals (title)
values ('Perform Data Backups'),('Network Maintenance'),
('Data Capturing'),('Client Correspondence');

create table rating_employee
(
user_id INT NOT NULL,
goal_id INT NOT NULL,
rating SMALLINT NOT NULL,
foreign key (goal_id)
references goals (id),
foreign key (user_id)
references users (id)
);

insert into rating_employee
values (1,1,3),(4,4,4),(1,2,5),(4,3,4);

create table rating_manager
(
goal_id INT NOT NULL,
manager_id INT NOT NULL,
rating SMALLINT NOT NULL,
foreign key (goal_id)
references goals (id),
foreign key (manager_id)
references users (id)
);
insert into rating_manager
values (1,3,4),(2,3,3),(3,3,4) , (4,3,5);

If the user is a manager you can list ratings for the user and , the rating that manager the manager made for the users with the following query:

select g.title,
g.id,
u.username,
e.rating as "User Rating",
m.rating as "Manager Rating"
from
goals as g
inner join rating_employee as e
on e.goal_id = g.id
inner join users as u
on u.id = e.user_id
inner join rating_manager as m
on g.id = m.goal_id
where m.manager_id = 3
order by u.username

The limitation i see is that when a user shares a goal with another user, that the query wouldn't return ratings for each individual.

Any advice on a design that could solve the limitation?