Relationships between tables. SQL Server

Question

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?

2

база-данных sql-server sql

Author: The Konstantin, 2020-05-05

Source

1 answers

score 0 · Answer 1

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.