Tell me, I start correcting the database correctly:
While there are 3 tables: dbo.exam
, dbo.offset
, dbo.session
.
exam and test similar structure: ID
name
date
.
DBO.SESSION
: ID
fromdate
Todate
session one, but it can have several tests and exams.
As I assume, I need to make a connection many to many .
so I do the following:
create table dbo.studentsession
(
Sessionid int Foreign Key References DBO.Session (ID)
Examid int Foreign Key References DBO.exam (ID),
Offsetid Int Foreign Key References DBO.offset (ID)
Constraint ID Primary Key (Sessionid, Examid, OffsetID)
)
p.s How can I refer to id
dbo.studentsession
from other tables?
Answer 1
If you claim that one session may contain multiple exams and tests , then these are two connections one to many .
More efficient to create tables Sessionexam sessionoffset , respectively, from Sessionid, Examid
and SessionID, Offsetid
. This will not make it possible to produce entries in the future table StudentSession . Accordingly, then from these tables on the external key sessionid
you can get all the necessary information about the exams and tests by ID session.