I currently have 2 tables STUDENTS
and SUBJECTS
. I need to create another table for ENROLLMENTS
, the thing is that in that table I need to know the minimal attendance, which is both dependent on student (if he has a job it will be lower) and on subject (each subject has a base minimal attendance). Should I create another table like MINIMAL_ATTENDACE
which holds the base minimal attendance of each Subject ? If so, is it good to have the primary key in that table be also the primary key from SUBJECTS
?
For example I have :
--STUDENTS--
ID NAME HIRED
1 Paul 1
--SUBJECTS--
ID NAME NUMBER_OF_TESTS M_ATTENDANCE
1 Math 2 10
--ENROLLMENTS--
ID STUDID SUBID M_ATTENDANCE
1 1 1 7 (Because Student is hired)
Advertisement
Answer
You can use a several constraints on a single column. Apparently, there is a many-to-many relationship between STUDENT and SUBJECT. A possible approach for implementing this (with SQL) is an “intersection” or “bridge” table is. You can use a composite PK, and foreign key constraints like this:
Demo (dbfiddle)
-- parent tables (and test data)
create table students (id primary key, name, hired)
as
select 1, 'Paul', 1 from dual union all
select 2, 'Maggie', 1 from dual union all
select 3, 'Herbert', 0 from dual ;
create table subjects (id primary key, name, number_of_tests, attendance_standard, attendance_hired)
as
select 100, 'Maths', 2, 10, 7 from dual union all
select 200, 'Biology', 3, 12, 8 from dual union all
select 300, 'Physics' 2, 10, 8 from dual ;
-- intersection
create table enrollments(
studentid number references students( id )
, subjectid number references subjects( id )
, constraint enrollments_pk primary key( studentid, subjectid )
) ;
-- test data for the "intersection" table:
-- every student needs to complete every subject
insert into enrollments
select S.id, SUB.id
from students S cross join subjects SUB ;
In the example (above), we assume that {1} the “hired” attribute belongs to a STUDENT, and {2} the different “attendance” attributes are attributes of SUBJECT.
You can now just join the 3 tables, and find the required attendance for each student using the query below.
-- Get the attendance requirements for each student & their chosen subject
select
S.name, S.hired
, SUB.name
, case
when S.hired = 1 then SUB.attendance_hired
else SUB.attendance_standard
end as attendance_required
from students S
join enrollments E on S.id = E.studentid
join subjects SUB on SUB.id = E.subjectid
;
-- output
NAME HIRED NAME ATTENDANCE_REQUIRED
Paul 1 Maths 7
Maggie 1 Maths 7
Herbert 0 Maths 10
Paul 1 Biology 8
Maggie 1 Biology 8
Herbert 0 Biology 12
Paul 1 Physics 8
Maggie 1 Physics 8
Herbert 0 Physics 10
If the “hire” status of a STUDENT can change during an academic year (or semester), you’d probably need to add some more entities (and subsequently: tables) for keeping track of each STUDENT’s hire state change.