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)
- group of schools
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.
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.