php - 表设计及相关sqli查询

Question

There are two drop down lists in the page

1. 中心(显示所有中心)
2. 类(class)(显示所有类(class))

When the user selects a center from drop down menu the result should display all the courses run by that center only

Similarly when the user selects a course name from the drop down list, it displays all the centers running that Particular course

现在的问题是

1. 如何设计表格
2. 如何描述它们之间的关系
3. 应该使用哪些相关 SQL 查询来实现上述(突出显示的)功能？

Answer 1

架构

centers (id, name)
courses (id, name, id_center)

显示与某个中心相关的所有类(class)

select id, name 
from courses 
where id_center= 'user_center_selection';

显示与类(class)相关的所有中心(使用 in 和 subselect )

select id, name 
from centers
where id in (select id_center 
          from courses where  id = 'user_course_selection')

或者使用内连接

select id, name 
from centers 
inner join courses on 
      (courses.id_center = enters.id and courses.id = 'user_course_selection')