mysql - Relational Database design issue -


while designing relational database got questoin in head if design made made easier. design consists of 4 tables: (simplified)

  • location
  • office
  • school
  • group of schools

erd pk = primary key, fk = foreign key

lets want details location. if location school want see name, amount of students , name of group of schools part of. if office want see name of office. current design impossible in 1 query (with knowledge).

question: is there better way design database can needed details location in 1 query.

although there may way these details in 1 query more interested in enhancing database design.

thanks in advance, knarfi

answer: found term describes problem: polymorphic association. this question gave me answer.

you can try this

select location.*,school.*,office.* location left join school on school.locationid=location.locationid left join office on office.locationid=location.locationid

in result have null values school fields if there office , vice versa. have check in application , make decision there - database sends data.


-