Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 How can I tie these tables together?

Author  Topic 

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2013-05-29 : 23:48:05
Greetings Experts,

I have following table with attributes:

tblCourseInstructor
CourseId int NULL,
InstructorId int NULL

tblCourses
CourseId int IDENTITY(1,1) NOT NULL,
CourseName nvarchar(100) NULL,
CourseDescription nvarchar(2000) NULL

tblInstructors
InstructorId int NULL,
InstructorName nvarchar(50) NULL



tblLocations
LocationId int IDENTITY(1,1) NOT NULL,
Location nvarchar(50) NOT NULL,
Seating_Capacity int NULL,
offers_inclass_training bit NULL,
offers_online_training bit NULL,
Available_Seating int NULL



tblTrainingType
TrainingTypeId int IDENTITY(1,1) NOT NULL,
TrainingType nvarchar(50) NOT NULL

History:

We are trying to create a training program at work where employees can either take courses online or in class training.

That's why we came up with the tables above.

There is a main table where registered users and their registration details will be stored.

The Challenges:

What we would like to do is present Registration information page.

This page will ask employees to choose training location from the dropdown.

Once a training location is selected, details of the training for that location are displayed in a tabular format.

For instance, the name of course, the instructor, course description, and date and time of course.

If the employee is interested in this course, s/he will click a button to register this course.

Our big challenge is to tie the tables I listed above so that when we query the tables, we are able to list the coursenames, instructors, locations, date and time, etc as described above.

Based on the tables I listed above, no key to tie all together.

Can someone please help me with what I could possibly be missing?

Thanks alot in advance.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-05-30 : 00:33:01
--May be this way...
CREATE TABLE tblCourses (
CourseId int IDENTITY(1,1) NOT NULL PRIMARY KEY,
CourseName nvarchar(100) NOT NULL,
CourseDescription nvarchar(2000) NULL)

CREATE TABLE tblInstructors (
InstructorId int NOT NULL PRIMARY KEY,
InstructorName nvarchar(50) NOT NULL )

CREATE TABLE tblCourseInstructor
( CoursInstrId INT IDENTITY(1,1) PRIMARY KEY,
CourseId int NULL REFERENCES tblCourses(CourseId),
InstructorId int NULL REFERENCES tblInstructors (InstructorId))

CREATE TABLE tblTrainingType (
TrainingTypeId int IDENTITY(1,1) NOT NULL,
TrainingType nvarchar(50) NOT NULL
)

CREATE TABLE tblLocations (
LocationId int IDENTITY(1,1) NOT NULL ,
Location nvarchar(50) NOT NULL,
Seating_Capacity int NULL,
offers_inclass_training bit NULL,
offers_online_training bit NULL,
Available_Seating int NULL,
CoursInstrId INT NOT NULL REFERENCES tblCourseInstructor(CoursInstrId)
)


--
Chandu
Go to Top of Page

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2013-05-30 : 00:48:30
Thank you for the prompt response.

Two questions.

If I select location as indicated, how is that going to load courses, instructors, date time, etc?

Also, what about tblTrainingType?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-30 : 01:11:19
I think what you need is a training schedule table like this

CREATE TABLE tblTrainingSchedule (
TrainingScheduleId int IDENTITY(1,1) NOT NULL ,
DateOfTraining datetime NOT NULL,
StartTime datetime NOT NULL,
Duration decimal(4,1),
LocationId int NOT NULL REFERENCES tblLocations(LocationId),
CoursInstrId INT NOT NULL REFERENCES tblCourseInstructor(CoursInstrId)
)


This will have details of each training like date of training,start time, duration and location where its presented. It also has courseinstr details included which will help you to link and get course and instructor details. You could also add any other session specific details in this table.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-30 : 01:14:04
quote:
Originally posted by bandi

--May be this way...
CREATE TABLE tblCourses (
CourseId int IDENTITY(1,1) NOT NULL PRIMARY KEY,
CourseName nvarchar(100) NOT NULL,
CourseDescription nvarchar(2000) NULL)

CREATE TABLE tblInstructors (
InstructorId int NOT NULL PRIMARY KEY,
InstructorName nvarchar(50) NOT NULL )

CREATE TABLE tblCourseInstructor
( CoursInstrId INT IDENTITY(1,1) PRIMARY KEY,
CourseId int NULL REFERENCES tblCourses(CourseId),
InstructorId int NULL REFERENCES tblInstructors (InstructorId))

CREATE TABLE tblTrainingType (
TrainingTypeId int IDENTITY(1,1) NOT NULL,
TrainingType nvarchar(50) NOT NULL
)

CREATE TABLE tblLocations (
LocationId int IDENTITY(1,1) NOT NULL ,
Location nvarchar(50) NOT NULL,
Seating_Capacity int NULL,
offers_inclass_training bit NULL,
offers_online_training bit NULL,
Available_Seating int NULL,
CoursInstrId INT NOT NULL REFERENCES tblCourseInstructor(CoursInstrId)
)


--
Chandu


Couple of issues i see with this

1. Cant represent location which doesnt have training associated yet
2. If location has multiple training sessions all location details has to be repeated here which may cause chance for update anomalies

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-05-30 : 01:28:47
hi visakh,
I agree with you..
Is tblTrainingType table required?
tblLocations table have two columns acting as indicator for either online/offline or both

--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-30 : 01:42:35
quote:
Originally posted by bandi

hi visakh,
I agree with you..
Is tblTrainingType table required?
tblLocations table have two columns acting as indicator for either online/offline or both

--
Chandu


Hmm...How is that going to specify which training course, instructor,starttime,duration etc details?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-05-30 : 02:07:46
Hi visakh,
You have provide the table tblTrainingSchedule.. by this we can...
I asked about tblTrainingType table...

--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-30 : 02:10:05
quote:
Originally posted by bandi

Hi visakh,
You have provide the table tblTrainingSchedule.. by this we can...
I asked about tblTrainingType table...

--
Chandu


ah...sorry my bad...though you were referring to my table
TrainingType is still required if you want categorization of training sessions like HR Training, User Training, Soft Skills, Technical etc and it cant be linked to Courses to indicate which category each courses belong to

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-05-30 : 02:20:47
As per OP requirement, Training Type is Online or Offline ( I guess)

--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-30 : 02:32:25
quote:
Originally posted by bandi

As per OP requirement, Training Type is Online or Offline ( I guess)

--
Chandu


thats not Training Type but its Training Mode in actual case

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2013-05-30 : 08:41:27
Thanks to you both for all your time and assistance.

I understand the trainingSchedule. I do have that as indicated in my original post. I think I did mention it there.

However, what I am still having problem understanding is how to load everything initially.

For instance, per our requirement, we are to present users with a dropdown of locations.

When a user chooses one location, the course, the instructor, date and time of training and a small description of the course are listed.

The user can then click to register for any course that fits his or her need and schedule.

So, my concern is if I load the locations into a dropdown and a user selects say location 1, how does that relate to the course table, instructor table, datetable, etc in a query and display those.
for the user?

The trainingSchedule is supposed to house user's selection when the user submits his or her training choices.

I hope I am being clear with my need.

Thanks again to you guys.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-31 : 02:09:08
The intention behind tblTrainingSchedule was not to store user selections. This table is used for linking locations to training they offer. So once a user selects a location using this table( by means of locationid) you will be able to link to other tables and show training course,instructor etc details. User selections have to captured in another table like UserTrainingDetails which will have UserId (to identify users),TrainingScheduleId (which session he's selected). An User may have multiple selection which will add as multiple rows in the table.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -