Sql table design issue. One table that contains two table keys

Question

3.00/5 (1 vote)

See more:

I have two tables : Teacher and Lesson

Every teacher may have one or more lessons i create a new table and named it TeacherLesson :

SQL

CREATE TABLE [dbo].[TeacherLesson] (
[Id]        INT IDENTITY (1, 1) NOT NULL,
[teacherID] INT NULL,
[LessonID]   INT NULL,
PRIMARY KEY CLUSTERED ([Id] ASC));

when user try to add a new teacher he/she can choose lessons for new teacher so i have a list of lessons that user can select them. that means i already have the lesson id but what about TeacherID
teacherID in teacher table is an identity specification (auto int) field so i don't know what is next id ! how i can insert new record in TeacherLesson Table with teacherID and LessonID
note that i use linq to SQL and WPF C#!

can i use foreign-key an make teacherID foreign-key and do something like this ? :

C#

Teachers.TeacherLesson.add(newTeacherLesson);

is it technically OK ?

thank you!

What I have tried:

SQL Table design Issue. one table that contains two table Keys

Posted 31-Jul-16 11:37am

mamad1166

Updated 31-Jul-16 22:55pm

Add a Solution

4 solutions

Solution 1

Have a look at using ExecuteScalar function to get the ID.

C#

Int32 newId = (Int32) myCommand.ExecuteScalar();

Check out
Retrieving Identity or Autonumber Values[^]

Posted 31-Jul-16 11:44am

RossMW

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Animesh Datta · Accepted Answer · 2016-07-31T19:16:00

Hello ,
I think you can create 3 Tables for your requirement .
a. Lesson
b. Teacher
3. TeacherLesson (relation table between Teacher and Lesson )
Now , First insert data in Lesson table as you already have lessons . Now , when you are inserting data in Teacher at the same time insert record in TeacherLesson Table also means first insert data in Teacher table and then retreive the last inserted TeacherId from that table and then insert Lessons one by one for the TeacherId and the structure of TeacherLesson should be same as your definition means

CREATE TABLE [dbo].[TeacherLesson] (
[Id]        INT IDENTITY (1, 1) NOT NULL,
[teacherID] INT NULL,
[LessonID]   INT NULL,
PRIMARY KEY CLUSTERED ([Id] ASC));

and for get last-inserted identity value you may use

SQL

@@IDENTITY

For more details Reference : Here

Hope it helps you .

Thanks

mamad1166 · Accepted Answer · 2016-07-31T22:37:00

thank you very much for your answer

i use LINQ and WPF-C# not T-SQl !

the solution that i already use works but i want to know if it is technically OK and best for LINQ C# ?

C#

public static void InsertOrUpdateTeacher( string Name,string Family
,string Licence ,  byte[] Bytes,List<lesson> Lessons)
        {
 
            DataClassesDataContext dc = new DataClassesDataContext();
                Table<teacher> TeacherTable = dc.GetTable<teacher>();
                Teacher teacher = new Teacher();
                teacher.Name = Name;
                teacher.Family = Family;
                teacher.Licence = Licence;
                teacher.Image = Bytes;
                foreach (Lesson item in Lessons)
                {
                    TeacherLesson tl = new TeacherLesson();
                    tl.LessonID = item.Id;
                    Teacher.TeacherLessons.Add(tl);
                }
                TeacherTable.InsertOnSubmit(Teacher);
                TeacherTable.Context.SubmitChanges();
 

           
          
 
        }
</teacher></teacher></lesson>

and this is TeacherLesson table with Foreign-key

SQL

CREATE TABLE [dbo].[TeacherLesson] (
    [Id]        INT IDENTITY (1, 1) NOT NULL,
    [teacherID] INT NULL,
    [LessonID]   INT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [FK_TeacherLesson_ToTable] FOREIGN KEY ([teacherID]) REFERENCES [dbo].[Teacher] ([Id])
);

zcf-prince · Accepted Answer · 2016-07-31T22:55:00

Solution 4

ISNULL(SCOPE_IDENTITY(), 0)

Best to write a transaction, to guarantee the integrity of the whole

Posted 31-Jul-16 22:55pm

zcf-prince

Updated 31-Jul-16 22:57pm

v2

Sql table design issue. One table that contains two table keys

4 solutions

Solution 1

Solution 2

Solution 3

Solution 4

Add your solution here

Preview 0