CODEWITHSUNDEEP
sql-serverdatabasedatabase-designentity-relationship
CAD
CAD

Reputation: 4292

Multi relationship issue in a database design

I'm new to database design and now I have a requirement to develop a database to store meeting information. Basically the requirement is we have meeting rooms and our employees can book meeting rooms and they can specify participants for those meetings.

Participants are also employees The employee who booked a meeting room may or may not be a participant In a booking we have to store other information like start time, end time and name of the meeting. An employee can book any number of meetings For a meeting there can be one or many participants

Following is the database diagram proposed for the above requirement...

enter image description here

PROBLEM 01: Are there any issues in this design?

Now if I want to query the database to get following details...

enter image description here

... I could write...

SELECT MeetingRoom.name AS 'Room Name',Booking.name AS 'Meeting Name',Booking.user_id AS 'Booked By',Booking.start_time,
Participation.user_id AS 'Participant ID',Employee.name AS 'Participant Name' 
FROM MeetingRoom LEFT JOIN Booking ON MeetingRoom.room_id = Booking.room_id
LEFT JOIN Participation ON Booking.booking_id= Participation.booking_id
LEFT JOIN Employee ON Participation.user_id = Employee.employee_id

PROBLEM 02: In the booked by column I'm showing only the employee_id. But I'm unable to show the employee name instead. (if I need to show the name of the employee who booked the meeting room) Should I change the query / design to cater this requirement?

NOTE: Here in the database design, Employee has direct relationship with booking as employees can book meetings. Also employee has another relationship to booking through Participation as employees can participate to the meetings once booked.

Upvotes: 0

Views: 583

Answers (1)

jlee-tessik
jlee-tessik

Reputation: 1520

Problem 1: If I'm reading your design right, booking.user_id = employee.employee_id. If so, rename it to booking.employee_id, to make it easier to know where it's coming from.

Problem 2: You can join to the same table more than once in a query. Use an alias to differentiate between the two table usages, something like this:

SELECT MeetingRoom.name AS 'Room Name',Booking.name AS 'Meeting Name',Booking.user_id AS 'Booked By ID', Booker.name as 'Booked By Name', Booking.start_time,
Participation.user_id AS 'Participant ID',Employee.name AS 'Participant Name' 
FROM MeetingRoom LEFT JOIN Booking ON MeetingRoom.room_id = Booking.room_id
JOIN Employee as Booker ON MeetingRoom.User_ID = Booker.employee_ID
LEFT JOIN Participation ON Booking.booking_id= Participation.booking_id
LEFT JOIN Employee ON Participation.user_id = Employee.employee_id

Upvotes: 1

Related Questions