CODEWITHSUNDEEP
sqloracle-database
Paul
Paul

Reputation: 966

Foreign key as primary key?

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)

Upvotes: 1

Views: 2069

Answers (3)

stefan
stefan

Reputation: 2252

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:

enter image description here

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.

Upvotes: 1

Lajos Arpad
Lajos Arpad

Reputation: 76436

You can have a primary key also a foreign key, in order to enforce the rule that the second table does not have an element which is missing from the first table. However, mixing up foreign key with primary key this way is usually a bad idea, because your rule might change. It is customary to make a field of a composite primary key also a foreign key particularly in many-to-many tables, but unless you are worried of storage space, you might want to create a foreign key on a separate field, so you will have less worries if your rule changes.

However, what you intend to store can be computed with aggregation, you do not necessarily need a separate table for that. You just group by and select min. You might also create a view if that's better, or, if changes on the main table are frequent, then you might consider the creation of a materialized view.

Upvotes: 0

Gerard H. Pille
Gerard H. Pille

Reputation: 2578

If the problem is as simple as you describe, it would be sufficient if the subject has two minimal attendences, ie. one for students with, and one for students without jobs.

Upvotes: 1

Related Questions