Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2010
    Posts
    2

    Unanswered: Should i use recursion?

    Hi all,

    I'm designing a little DB solution and seem to be stuck...
    What I have so far is in an ERD jpg attached.

    I basically need to capture the fact that delegates on the course instance might be a part of a group, with a designated group leader, allowing for more than one group to be on a course instance... The way I see it, seems wrong...
    What do people think of that? Are there any other suggestions?

    Thank you...
    Attached Thumbnails Attached Thumbnails proposedERD.jpg  

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by dimmslider
    Are there any other suggestions?
    Few points:
    • Strictly speaking that's not recursion. If groups could be made of other groups then that would be recursion.
    • If this is homework then this should stated quite clearly before detailing your problem.
    • I can't see any foreign key fields in any of your tables which makes it difficult (for me) to see how things tie together.
    • Is GRP_Leader a field that holds the id of a delegate, a client or a trainer.
    • Some details on where the course is being held etc might be useful.
    • Could trainer and client be put into a common table with a type field?
    • Not putting the details of the company that runs the courses into a table means that this stuff has to be hard coded into the programs - which is usually poor.

  3. #3
    Join Date
    Jan 2010
    Posts
    2
    Oh, I did not realize that I had to say that it was homework...(Forum rules are quite concise...)

    It is homework.

    I suppose that lumping clients and trainers into 1 could be a sane idea.

    The table company holds info about companies that a client might be booked by. So, the client table has a FK pointed at the companyID.

    All courses are run by the same company.

    One more thing, here is a select statement,
    Below it is the output, maybe that'll make things a little clearer.
    Code:
    select client.CLI_Forename + ' ' + client.CLI_Surname as [Client Name],
    company.CO_CompanyName,
    delegate.DEL_Date, 
    course.C_Code, 
    course.C_Title,
    course.C_Cost as Cost,
    course.C_Duration as Duration,
    trainer.Tr_Forename + ' ' + trainer.Tr_Surname as [Trainer Name],
    delegate.DEL_GroupID, 
    delegate.DEL_GroupLeader
    from client 
    left join delegate on delegate.DEL_clientNumber=client.CLI_clientNumber
    left join course on course.C_Code = delegate.DEL_courseCode
    left join [course instance] on course.C_Code = [course instance].CI_courseCode
    left join trainer on [course instance].CI_Trainer = trainer.Tr_TrainerID
    left join company on client.CLI_Company = company.CO_id
    order by client.CLI_Surname;
    Output:
    Code:
    Client Name                                                             CO_CompanyName                           DEL_Date                C_Code C_Title                        Cost                  Duration Trainer Name                                        DEL_GroupID DEL_GroupLeader
    ----------------------------------------------------------------------- ---------------------------------------- ----------------------- ------ ------------------------------ --------------------- -------- --------------------------------------------------- ----------- ---------------
    Denise Cartwright                                                       NULL                                     2010-03-12 00:00:00.000 MP01   RedMan - projects              865.00                3        Bob Symonds                                         0           0
    Bob Simonds                                                             NULL                                     2010-12-31 00:00:00.000 CB01   Converting to Redbase          390.00                2        Katie Fawset                                        0           0
    John Travolta                                                           SuperCompany ltd                         2010-04-16 00:00:00.000 DP21   RedBase Programming            1200.00               5        Katie Fawset                                        1           0
    Andrew Walker                                                           NULL                                     2010-12-31 00:00:00.000 CB01   Converting to Redbase          390.00                2        Katie Fawset                                        1           1

    Also, see the new ERD where I have added FKs.
    Attached Thumbnails Attached Thumbnails proposedERD.jpg  

  4. #4
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by dimmslider View Post
    The table company holds info about companies that a client might be booked by. So, the client table has a FK pointed at the companyID.
    The FK fields themselves should really appear in the list of fields for the table. The text FK_Client_COL_Comp_4DGF684K tells me very little.

    Actually the naming convention you use for your tables and fields could be improved. Would it be better to start a table name in upper case to distinguish it from the a field name. Why start all the field names with the code for the table? Looking at your SQL the DEL in delegate.DEL_GroupID tells me that the field is in the delegates table but then so does the word delegate! Wouldn't it be better to indicate which table this group id is in? I accept that the database is very small but I'm sure you could picture problems if the database had 100's of tables. It is good though to have standards so I'll commend you on that!

    Quote Originally Posted by dimmslider View Post
    I suppose that lumping clients and trainers into 1 could be a sane idea.
    Just something to think about really. If you imagine the work involved in producing a screen etc to fully maintain two tables and provide searching etc then this work can be halved by having the data in one table. A primary aim of all databases is to logically organise your data so does it make sense to have tel numbers for one type of person in one table and tel numbers for other people in another table. If you just figure that clients and trainers are just types of people then you can put them in the same table. This isn't a hard rule but, as I say, it's worth thinking about.

    Quote Originally Posted by dimmslider View Post
    All courses are run by the same company.
    The reason it's good to put the name of the company and any other details into a table is because the system may be used by other companies in future (unlikely with coursework I'll admit) or the company details may change to perhaps lot's of training companies might want to share the system. This is all straightforward to do if you have such a table but if you don't it means rewriting the code to reflect each change.

    Quote Originally Posted by dimmslider View Post
    I did not realize that I had to say that it was homework...(Forum rules are quite concise...)
    People put a lot of effort into helping others on the site. If they felt they were continually just helping students do their coursework then I'm sure you could understand that they'd offer their services less and the whole community would suffer. Obviously the aim of your coursework is to make you think rather than make me think. As you point out there are only 2 simple rules on the site which cover all occasions and as a moderator I'd like to ask you to just mention in any future questions that it's coursework (see rule 1)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •