Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 334
  • Last Modified:

Help with Database Scheme/Structure

Hello Experts,

I need to create a web application for users to sign up for Online Courses. But before I start I would like to get some feed back in regards to how I should setup the Database Scheme before coding. All entries in the COURSES TABLE are all unique (one entry per Course). As for COURSE PROGRAMS TABLE their may be multiple programs for a particular COURSE but then some COURSES will NOT have multiple Programs and I'm not sure if the scheme I have below will work for COurses that DO NOT have more than one Program.

Below is my table structe that I currently have setup. Please tear it apart and tell me what I may be missing and explain why so I understand :) I'm VERY concerned with the relationship between COURSES TABLE, COURSES PROGRAMS, AND COURSE SCHEDULE.



COURSES TABLE:

SAMPLE DATA:
ghaoc_id     ghaco_name                    ghaco_desc
1            Eat Well for Life I           Eat Well for life....
2            Drop 10 in 10                 Drop 10 ......
3            Nutrition Management          Management .......
4            Weight Watchers               Weight .......
5            Discover Relaxation Within    Discover ...........




COURSE PROGRAMS TABLE:

SAMPLE DATA:
p_id         ghaoc_id                      p_name
111          1                             LIVE ONLY
112          1                             ONLINE ONLY WITH BOB
113          1                             ONLINE ONLY WITH JEFF
114          5                             Stess Relive
115          5                             Walking
116          4                             N/A
117          3                             N/A
118          2                             N/A





INSTRUCTOR TABLE:

SAMPLE DATA:
hmi_id    hmi_name
200       John Doe
201       Mitch Doe
202       Sally Doe
203       Harry Doe
etc...




COURSE SCHEDULE TABLE:

SAMPLE DATA:
csch_id    ghaoc_id    p_id    hmi_id    csch_startdate    csch_starttime    csch_endtime
10         1           111     200       09/21/2011        3:30              4:30
11         1           111     200       09/22/2011        3:30              4:30
12         1           111     200       09/25/2011        3:30              4:30
13         1           111     200       10/01/2011        3:30              4:30
14         1           111     200       10/03/2011        3:30              4:30
15         1           111     200       11/04/2011        3:30              4:30
16         5           114     202       11/06/2011        3:30              4:30
17         5           114     202       11/08/2011        3:30              4:30
18         5           114     202       11/10/2011        3:30              4:30
19         5           115     203       12/11/2011        3:30              4:30
20         5           115     203       12/13/2011        3:30              4:30
21         5           115     203       12/14/2011        3:30              4:30
22         4           116     203       12/14/2011        3:30              4:30
23         2           118     203       12/14/2011        3:30              4:30
24         3           117     203       12/14/2011        3:30              4:30
0
asp_net2
Asked:
asp_net2
  • 8
  • 8
1 Solution
 
HainKurtSr. System AnalystCommented:
looks good to me...

COURSE PROGRAMS TABLE will hold one / none (maybe not added yet or inactive etc) or multiple programs for a given course
0
 
LlamaJoeCommented:
always remember to "take the one to the many" and you'll be fine.

i.e., you took the 1 unique course into the course program table, then took the 1 unique instructor  into the course schedule class which is the "many" in the diagram.  You are no longer restricted on column label sizes, so I would give you columns much longer names, e.g.,
Schedule_ID, Course_ID, Instructor_ID, Course_Program_ID, etc.

Actually, you don't even need a schedule_id - as each course entry is unique - AND, you can get rid of the course column (ghaoc_id) since it is identified through the foreign key to the course program table through the p_id.


0
 
HainKurtSr. System AnalystCommented:
LlamaJoe is right :) ghaoc_id is redundant and should be removed from your last table...
if you keep, your data will be replicated and it may create maintenance issue later...
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
asp_net2Author Commented:
That all may look fine and it does to me as well. But my worries have to do with how I relate COURSE PROGRAMS to the COURSES TABLE since some courses will NOT have ANY programs associated to them at all. However, in my code I need to retrieve ALL COURSE PROGRAMS that relate to a particular course in the COURSES TABLE. Also, when I retrieve data from the COURSE SCHEDULE TABLE I need to have a button that when the user clicks on it I need to submit ghaoc_id and p_id to the Database in another table so I can collect which Course and Program the user wants to schedule for.

Please see the attached file, I added the data and table to it to kee the formatting nice.  ee.txt
0
 
asp_net2Author Commented:
@HainKurt / @LlamaJoe,

Can either of you still help me out?
0
 
HainKurtSr. System AnalystCommented:
I need to retrieve ALL COURSE PROGRAMS that relate to a particular course in the COURSES TABLE

select * from COURSE_PROGRAMS cp where ghaoc_id = @ghaoc_id

when I retrieve data from the COURSE SCHEDULE TABLE I need to have a button that when the user clicks on it I need to submit ghaoc_id and p_id to the Database in another table so I can collect which Course and Program the user wants to schedule for.

just pass @csch_id

--get p_id & ghaoc_id
select @ghaoc_id = ghaoc_id, @p_id=cs.p_id from COURSE_SCHEDULE cs inner join COURSE_PROGRAMS cp on cp.p_id=cs.p_id
where cs.csch_id = @csch_id

--or get them seperately
select @p_id = p_id from COURSE_SCHEDULE where cs.csch_id = @csch_id
select @ghaoc_id from COURSE_PROGRAMS cp where cp.p_id=@p_id

--then get course
select * from COURSE_PROGRAMS cp where p_id = @p_id

--then get course programs
select * from COURSES c where ghaoc_id = @ghaoc_id
0
 
HainKurtSr. System AnalystCommented:
last two comments (or queries) should be reversed :)

--then get course
select * from COURSES c where ghaoc_id = @ghaoc_id

--then get course programs
select * from COURSE_PROGRAMS cp where p_id = @p_id
 
0
 
asp_net2Author Commented:
Thanks for all your help HainKurt. I'm still a little confused. What would be the best approach if I don't have any programs associated to a Course? I try to think of it as signing up for College Classes. You have to choose the Type of Course "Technology" but then there are a variety of "Programs" that fall under "Technology". However, some Courses may not have any Programs and that is what I'm having a hard time understanding how I should handle this. If you like at the attached file I uploaded, you will see the following below. You will notice if you look at the attached file that ghaoc_id value "2" below is related to  Drop 10 in 10 which does NOT have any Programs associated to it along with Nutrition Management and Weight Watchers. So how can I add N/A to the Database when placing an order for purchase? Do you see what I mean?

COURSE PROGRAMS:
116          4                             N/A
117          3                             N/A
118          2                             N/A
0
 
HainKurtSr. System AnalystCommented:
is there any such case like

there is no program for a course but there is schedule for that course?

you dont need to add 'N/A' record into COURSE PROGRAMS, you can derive that info all the time with LEFT JOIN query...

user will select course, then you list all programs, if there is no record nothing to add to basket :)
and if there is no program, there should not be any schedule (I guess)

here what I mean... and same queries will apply to this data structure (dont store N/A and dont add record for these N/A programs)
COURSES TABLE:

ghaoc_id     ghaco_name                    ghaco_desc
1            Eat Well for Life I           Description goes here....
2            Drop 10 in 10                 Description goes here....
3            Nutrition Management          Description goes here....
4            Weight Watchers               Description goes here....
5            Discover Relaxation Within    Description goes here....


COURSE PROGRAMS TABLE:

p_id         ghaoc_id                      p_name
111          1                             LIVE ONLY
112          1                             ONLINE ONLY WITH BOB
113          1                             ONLINE ONLY WITH JEFF
114          5                             Stess Relive
115          5                             Walking


INSTRUCTOR TABLE:

hmi_id    hmi_name
200       John Doe
201       Mitch Doe
202       Sally Doe
203       Harry Doe


COURSE SCHEDULE TABLE:

csch_id    ghaoc_id    p_id    hmi_id    csch_startdate    csch_starttime    csch_endtime
10         1           111     200       09/21/2011        3:30              4:30
11         1           111     200       09/22/2011        3:30              4:30
12         1           111     200       09/25/2011        3:30              4:30
13         1           111     200       10/01/2011        3:30              4:30
14         1           111     200       10/03/2011        3:30              4:30
15         1           111     200       11/04/2011        3:30              4:30
16         5           114     202       11/06/2011        3:30              4:30
17         5           114     202       11/08/2011        3:30              4:30
18         5           114     202       11/10/2011        3:30              4:30
19         5           115     203       12/11/2011        3:30              4:30
20         5           115     203       12/13/2011        3:30              4:30
21         5           115     203       12/14/2011        3:30              4:30

Open in new window

0
 
asp_net2Author Commented:
I see what  you mean, but I think you may be missing the problem I'm facing :( Let me explain in Steps below. I'm sorry for going on and on with this HainKurt but I TRULY APPRECIATE your help. I'm getting questioned and asked when I can have this done but have not fully answered yet until I know how to set this up for sure :( I really need your help!!!

STEP 1: User selects Course Name from Page.

STEP2: User sees all Programs that are related to the specific Course they selected in STEP1 along with scheduling. HOWEVER, Some Courses will NOT have any programs associated to them. In this case a user will still need to regiter but they will be registering just the Course with NO PROGRAMS. But I need to know what value to put into the Database below if NO PROGRAM exists.

STEP3: User clicks Register button after viewing the information from STEP 1 AND STEP 2. Once user click Button I need to place the following fields below into Database.

user_id    ghaoc_id     p_id
0
 
HainKurtSr. System AnalystCommented:
ok, use the values I posted, same structure, keep ghaoc_id in COURSE SCHEDULE, but put p_id NULL in such cases...
do not insert N/A record into COURSE PROGRAMS, just to make your life easy...

COURSE SCHEDULE TABLE:

csch_id    ghaoc_id    p_id    hmi_id    csch_startdate    csch_starttime    csch_endtime
...
22         4           NULL     203       12/14/2011        3:30              4:30
23         2           NULL     203       12/14/2011        3:30              4:30
24         3           NULL     203       12/14/2011        3:30              4:30

Open in new window

0
 
asp_net2Author Commented:
Hi HainKurt

Why would I have to use NULL instead of another value such as 2 that represents N/A?

Also, when a user fills out schedule they have to select course from a dropdown list that retries all course names from the database. The same thing applies for programs. So if no program is associated to the selected course then what do I show for the program value in the dropdown list?

0
 
HainKurtSr. System AnalystCommented:
i just thought not inserting N/A records & using NULL values when inserting into COURSE SCHEDULE would be much easier, and requires less maintenance & easy to code... if you think it is less coding then go ahead with that... it is just a preference... just make sure you add N/A records for all courses that do  not have programs and you should be fine...
0
 
asp_net2Author Commented:
Ok, would you mind if I try to do this and leave this post open for a few days to try this all out? This way if I would run into any problems I could post something back? If you don't mind check back daily.
0
 
HainKurtSr. System AnalystCommented:
ok, I will (please do not post anything unless if you really ask something on this thread :)
0
 
asp_net2Author Commented:
I will make sure if I post it has to do with what we are discussing.
0
 
asp_net2Author Commented:
Thank you for all your help HainKurt. It was very much appreciated!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 8
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now