Relational Database design issue


While designing a relational database I got the questoin in my head if the design I made could be made easier. The design consists of 4 tables: (simplified)

  • location
  • office
  • school
  • group of schools

ERD PK = primary key, FK = foreign key

Lets say we want to get details about a location. If the location is a school we want to see the name, amount of students and the name of the group of schools its part of. If it is a office we only want to see the name of the office. With the current design this is impossible to do in 1 query (with my knowledge).

question: Is there a better way to design this database so I can get the needed details about a location in 1 query.

Although there may be a way to get these details in 1 query I am more interested in enhancing the database design.

Thanks in advance, Knarfi

ANSWER: I finally found the term that describes my problem: Polymorphic Association. Especially this question gave me a good answer.


Answers:


You can try something like this

SELECT location.*,school.*,office.*
FROM location
LEFT JOIN school ON school.locationID=location.locationID
LEFT JOIN office ON office.locationID=location.locationID

In result you will have NULL values for school fields if there will be office and vice versa. But you have to check this in your application and make decision there - database sends you all data.