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.
| 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 NULLtblCourses CourseId int IDENTITY(1,1) NOT NULL, CourseName nvarchar(100) NULL, CourseDescription nvarchar(2000) NULLtblInstructors InstructorId int NULL, InstructorName nvarchar(50) NULLtblLocations 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 NULLtblTrainingType TrainingTypeId int IDENTITY(1,1) NOT NULL, TrainingType nvarchar(50) NOT NULLHistory: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 |
 |
|
|
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? |
|
|
|
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 thisCREATE 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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
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 this1. Cant represent location which doesnt have training associated yet2. If location has multiple training sessions all location details has to be repeated here which may cause chance for update anomalies------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
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 |
|
|
|
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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
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 |
|
|
|
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 tableTrainingType 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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
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 |
|
|
|
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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
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. |
|
|
|
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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|
|
How can I tie these tables together?