Correct way to insert values

Other SQL Server Topics
1

Good day all, to begin with, i am a complete beginner to this.
i have to create tables and insert statements for these tables. Basically i am using these two tables.

  1. Class whose attribute is classID (PK)
  2. Term whose attribute is Term ID (PK)

Class is pulling the term id (as a foreign key)
So it would be:

Insert into Class
Values ('classID001','TermID001');

My problem is that some classes are taken in 2 different terms. I am thinking of doing it this way (shown below) but i fear i WILL have constraints.

Insert into Class
Values ('classID002','TermID001,TermID002');

Please help me to see where i am going wrong. my understanding is very basic so i am sure i am missing something really simple.

I apologize for my lack of proper highlighting of code and poor categorizing.

2

you will need to insert 2 rows for two different terms for a class.

1 Like
3

You could have a "link" table.

Create a table for CLASS - ClassID, Class name, and any other relevant columns.

Then create a table for TERM - TermID, Term start / end date, name, etc.

Then create a link table with columns ClassID, TermID which allows any Class to be associated with (any number of) Terms.

I strongly recommend that you try to avoid a design that includes any columns that can contain multiple values - such as 'TermID001,TermID002' because it becomes a nightmare to report on, ensure data integrity and so on.

If you want to know what Classes are available in a specific Term you can do:

SELECT C.ClassID, C.ClassName, T.TermID, T.TermName, etc.
FROM CLASS AS C
    JOIN MyLinkTable AS L
        ON L.ClassID = C.ClassID
    JOIN TERM AS T
        ON T.TermID = L.TermID
WHERE T.TermID = 1234
1 Like
4

You can have 2 insert statements because you have a one to many relationship, i.e. 1 class has many terms.
create class table with classID as PK and Term Table with TermID as PK and Class ID as FK.

and then ,
Insert into Term values ('termID001','ClassID001')
Insert into Term values ('termID002,'ClassID001')

Hope this Helps !!

2 Likes