Relationships between tables. SQL Server
Tell me if I'm starting to build the database correctly:
So far there are 3 tables: dbo.Exam
, dbo.Offset
, dbo.Session
.
The exam and test have a similar structure: id
name
date
.
Structure dbo.Session
: id
fromDate
toDate
.
The session is one, but it can have several tests and exams.
As I guess, I need to make a connection Many to many.
So I do next:
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 then refer to id
dbo.StudentSession
from other tables?
1 answers
If you claim that one session can contain multiple exams and tests, then these are two connections one to many.
It is more efficient to create SessionExam tables SessionOffset, consisting of sessionId, examId
and sessionId, offsetId
, respectively. This will prevent you from creating records in the future StudentSession table. Accordingly, then from these tables, using the foreign key sessionId
, you can get all the necessary information about exams and tests for id sessions.