#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    77
    Rep Power
    3

    Is my relationships correctly optimized or...


    Hi folks,

    Just wanted to get some other eyes to look at my database to make sure the relationships and optimization is ok.

    I have added the mysql workbench project and a print out in pdf.
    Dropbox

    best,

    Johan
    Last edited by odp; October 23rd, 2013 at 02:59 PM. Reason: typo
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    77
    Rep Power
    3
    Are you guys hungry after sql code and a thorough explaination, or is my eer diagram complete opscure?
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,178
    Rep Power
    4274
    Originally Posted by odp
    ... or is my eer diagram complete opscure?
    yes

    i got a real crick in my neck trying to read it sideways, so i bailed
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    77
    Rep Power
    3
    Originally Posted by r937
    yes

    i got a real crick in my neck trying to read it sideways, so i bailed
    Hahaha gotcha, I'll make a new horizontal version for you

    Here you go
    Dropbox
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,178
    Rep Power
    4274
    the user_has_role many-to-many table should ~not~ have an auto_increment id -- instead, the PK should be a composite key consisting of the two FKs

    the qualifications table doesn't make sense... each qualification can have only one user??
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    77
    Rep Power
    3
    Sorry for the late reply, and thanks for the input.

    I have changed the user_has_role table .

    Regarding the qualification table it goes like this.

    A user has qualifications that are unique to that user. In the design those are for now languages (which eventually will derive from a seperate table) and a referencedBy.

    For instance:

    John speaks English and was referenced by Sally.

    Furthermore the user has experiences in certain sub-sectors that belongs to a few sectors. so make an example:

    John is an expert in Karate which is a Martial Art
    John is an expert in Ballet which is a Dance.

    So in the end I would like to query for:
    an user referenced by Sally, who speaks english and can Dance

    the result should be:
    John who speaks English, Dance ballet, he is an expert in Karate

    does it make sense?
  12. #7
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,178
    Rep Power
    4274
    Originally Posted by odp
    does it make sense?
    i dunno... please post the new diagram
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    77
    Rep Power
    3
    New Diagram
    Originally Posted by r937
    i dunno... please post the new diagram
  16. #9
  17. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,178
    Rep Power
    4274
    i don't understand the two side-by-side tables on the right, and not only because i don't understand the keys

    but i'm sure everything will go fine, good luck with your queries
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    77
    Rep Power
    3
    Originally Posted by r937
    i don't understand the two side-by-side tables on the right, and not only because i don't understand the keys

    but i'm sure everything will go fine, good luck with your queries
    You mean the sub_general and sub_sector?

    Thanks

IMN logo majestic logo threadwatch logo seochat tools logo