Visits
table - What kind of visits? many-to-many
meaning sort of museum trips related to a group of visitors (students,workers,tourists) or one-to-one
meaning some single person consultations with a doctor?
Visitors
table - I don’t think this table is necessary. Regardless of kind of a visitor (student, worker, teacher, tourist) your visitor_id
may be easily related to any external id
in Students
or Workers
or Teachers
etc tables and it is wrong to repeat already existing information.
NewVisitors
table - what do you need it for?
For one-to-one
I’d rather create:
Visits
table (id, ref, visitor_id, created_at, updated_at)
Students
table (id, name, grade_id, section_id, teacher_id, created_at, updated_at)
Teachers
table (id, name, created_at, updated_at)
Grade
table (id, ref)
Section
table (id, ref)
SELECT *
FROM visits
LEFT JOIN students ON visits.visitor_id = students.id
LEFT JOIN grades ON students.grade_id = grades.id
LEFT JOIN sections ON students.section_id = sections.id
LEFT JOIN teachers ON students.teacher_id = tearchers.id
WHERE students.grade_id = 'A'
where grades
and sections
are simple tables representing list of references.
This way you have 5 scopes represented by folders Visits
, Students
, Grades
, Sections
, Teachers
.
There is no need to create an additional VisitsGrades
scope, because your field grade
is already within the scope of Visit
by referenced student_id
.
For the case of many-to-many
situation changes:
Now you need the third scope, VisitsStudents
:
-
VisitsStudents
table (id, visit_id, student_id)
with slight modification of joined tables:
-
Visits
table (id, ref, created_at, updated_at)
-
Students
table (id, name, grade_id, section_id, teacher_id, created_at, updated_at)
SELECT *
FROM visits_students
LEFT JOIN visits ON visits_students.visit_id = visits.id
LEFT JOIN students ON visits_students.student_id = students.id
LEFT JOIN grades ON students.grade_id = grades.id
LEFT JOIN sections ON students.section_id = sections.id
LEFT JOIN teachers ON students.teacher_id = teachers.id
WHERE students.grade_id = 'A'
As you can see there is lots of JOIN
s in sql queries, so this is a case where it is wise to use prefixes to avoid table fields names conflicts, for example: id
→ std_id
.