SitePoint Sponsor

User Tag List

Results 1 to 16 of 16
  1. #1
    SitePoint Zealot
    Join Date
    Nov 2009
    Location
    Ontario, Canada
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Database Design Question

    I'm making a database for safety training and come across something I can't quite figure out how to do considering best practice.

    I have a table like so:

    courses
    id
    name
    frequency
    expires
    required

    My question is I have several locations where the required field may or may not pertain to.

    So should I setup the table like so:

    courses
    id
    name
    frequency
    expires
    loc1_req
    loc2_req
    loc3_req etc...

    I have about 15ish locations.

    Not sure how to go about this.

    I do have a separate table for the list of locations.

  2. #2
    SitePoint Zealot
    Join Date
    Nov 2009
    Location
    Ontario, Canada
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Actually, I just thought of another way to do this.

    I could make a new table called course_required like so:

    course_required
    course_id
    branch_id
    required

    Is this a better way of doing this?

  3. #3
    SitePoint Wizard rguy84's Avatar
    Join Date
    Sep 2005
    Location
    Durham, NC
    Posts
    1,659
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    so you have say 5 different courses, and 15 locations (I assume you mean like buildings on a campus, or different towns). Not each town needs every course...

    A table like what you have in post 2 would be best. I would have req_id, course_id, and branch_id. I assume your required field meant yes it is required?
    Ryan B | My Blog | Twitter

  4. #4
    SitePoint Zealot
    Join Date
    Nov 2009
    Location
    Ontario, Canada
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Correct, the required field would typically be a checkbox for yes/no.

    I'm still trying to figure out how to link that properly to the course table though.

  5. #5
    SitePoint Wizard rguy84's Avatar
    Join Date
    Sep 2005
    Location
    Durham, NC
    Posts
    1,659
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    Stop and think. The course-required table holds the courses REQUIRED by a location, wouldn't a field asking if course is required be redundant?
    Ryan B | My Blog | Twitter

  6. #6
    SitePoint Zealot
    Join Date
    Nov 2009
    Location
    Ontario, Canada
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    edit: ^^ beaten

    So I actually don't need required in the courses table.

    It would link like so:

    courses
    id
    name
    frequency
    expired

    locations
    id
    name

    course_required
    course_id
    location_id
    required

    and the select statement might look like:

    Code SQL:
    SELECT name FROM courses
    INNER JOIN course_required ON course.id = course_id
    INNER JOIN locations ON location_id = locations.id
    WHERE required = yes;

    Am I doing this right?

  7. #7
    SitePoint Wizard rguy84's Avatar
    Join Date
    Sep 2005
    Location
    Durham, NC
    Posts
    1,659
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Ryan Mortier View Post
    course_required
    course_id
    location_id
    required

    and the select statement might look like:

    Code SQL:
    SELECT name FROM courses
    INNER JOIN course_required ON course.id = course_id
    INNER JOIN locations ON location_id = locations.id
    WHERE required = yes;

    Am I doing this right?
    Read my post again. Course_required holds IDs of a course and location where that course is REQUIRED in that location, so all the rows would say yes cause Yes course 101 is required at location a, that is why it would be in a table listing the location with the required courses or in diffferent words all the courses with the required locations listed...
    Ryan B | My Blog | Twitter

  8. #8
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    You don't need a required field in the course_required table, unless you also want to store non required courses in that table

    Edit: what Ryan says

  9. #9
    SitePoint Zealot
    Join Date
    Nov 2009
    Location
    Ontario, Canada
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oh, I understand now. Perfect, thank you.

  10. #10
    SitePoint Wizard rguy84's Avatar
    Join Date
    Sep 2005
    Location
    Durham, NC
    Posts
    1,659
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    Edit: what Ryan says
    which one
    Ryan B | My Blog | Twitter

  11. #11
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by rguy84 View Post
    which one

  12. #12
    SitePoint Zealot
    Join Date
    Nov 2009
    Location
    Ontario, Canada
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm going to add onto this thread since I don't want to make a new thread for a (probably) silly question.

    Question is: How do I determine which table is the many table in a many to one or one to many relationship?

    Eg.

    Each employee can only hold one job title but each job title can be held by several employees (such as a receptionist position).

    However, if we look at it this way:

    Several employees can hold a job title but only one job title can be assigned to each employee.


    I think I'm just getting my logic messed up a bit from over thinking this.

    I would think the job title table would be the many table?

    Sorry, I'm new to this but eager to learn.

  13. #13
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    The first 'look' is the correct one You take one from a table, and see how many there can be in the other one.
    So, the employee table is the 'many' one in this case.

  14. #14
    SitePoint Zealot
    Join Date
    Nov 2009
    Location
    Ontario, Canada
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Alright, I think I understand now, so I should always start with one first.

    One employee can have one job title. - Wrong

    One job title can have many employees. - Right (Therefore the many table is the employee table)

    Is this correct?

  15. #15
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    One employee can have one job title. - so jobs is the '1' table.

    One job title can have many employees. - so employees is the 'many' table.

  16. #16
    SitePoint Zealot
    Join Date
    Nov 2009
    Location
    Ontario, Canada
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Got it, thanks for explaining it for me.


Bookmarks

Posting Permissions

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