SitePoint Sponsor

User Tag List

Results 1 to 15 of 15
  1. #1
    SitePoint Member
    Join Date
    Feb 2014
    Location
    netherlands
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Normalisation - start from scratch

    Well, not exactly. I do have in mind the views of data that has to be pulled from my database. For example: JobOpenings, CompanyProfiles, CompanyOpenings.

    Search can be on all columns to get a view of Job Openings regarding the column as a key search.

    My question is this:
    Should I start with ONE BIG table (about twenty columns), and work my way down to 3NF/4NF, or do I start with 3 tables from my View?

    Right now I have columns:
    Company | JobOpening | Province | City | Description | Skills | Branche | etc. Just all columns after each other...

    If I think about Job requirements: Skills | HourPw | etc
    Trouble with skills is that the choice is minimum 1 maximum 5, and each have 5 choices in it. So a separate table would be good, but against what key?

    If I think about Job location: Province | City | JobOpening | Company

    While I watched video's, read books and articles about normalisation, they all indicate one should already have separate tables. If that is true, I would at least need 3: JobOpenings, CompanyProfiles, CompanyOpenings.

    But in the end, I thought, it looks like many to many relationship is almost a table for every column...

    Any suggestions should be welcome

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    could you please explain the difference in job openings and company openings

    if i were to look at a report of job openings, would i see substantially the same data columns as a report on company openings?

    in other words, aren't they just openings?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Feb 2014
    Location
    netherlands
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi there,

    Job Openings would produce a list of ALL companies;
    Company Openings would produce a list of Openings AT this company in question

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yeah, that's what i figured... there would only be one openings table, but different queries would produce different subsets of the openings for varied reporting purposes

    you'd want a company table as well, so that company data isn't repeated for every opening that a company has

    ut's a classic one-to-many relationship, not many-to-many because a single job opening would not be for two different companies

    you probably also want a skills table, and here skills to openings would be many-to-many, so you'd need an intersection table to link specific openings and their skills
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    Feb 2014
    Location
    netherlands
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yes the skills trouble me. I need to make use of keys from the company table and jobopening table I guess.
    But since there are 5 entries ech for 5 levels I end up with repeating data or not?:

    skill1 | skill2 | skill3 | skill4 | skill5
    -------------------------------------------
    A1-5 | B1-5 | C1-5 | D1-5 | E1-5

    And what about if a Job requeres only 2 or 1?

  6. #6
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,871
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by bwakad View Post
    yes the skills trouble me. I need to make use of keys from the company table and jobopening table I guess.
    But since there are 5 entries ech for 5 levels I end up with repeating data or not?:

    skill1 | skill2 | skill3 | skill4 | skill5
    -------------------------------------------
    A1-5 | B1-5 | C1-5 | D1-5 | E1-5

    And what about if a Job requeres only 2 or 1?
    You'd restructure those five fields into two - skill number and skill. The skill number would contain 1 through 5 on separate records so as to put each of the 5 skills into a separate record rather than separate fields on the one record. .Makes changing things much easier when the number of levels increases to 6.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by bwakad View Post
    yes the skills trouble me. I need to make use of keys from the company table and jobopening table I guess.
    no, skills don't relate to companies, they relate to jobs

    like this --

    skills
    2 java
    3 c++
    5 python
    6 html
    7 css
    8 jquery

    companies
    22 acme inc
    24 delux corp
    25 giant co

    jobs
    3124 22 programmer
    3125 22 programmer/analyst
    3166 24 ui developer

    jobskills
    3124 2
    3124 6
    3125 2
    3125 6
    3125 8
    3166 6
    3166 7
    3166 8
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Member
    Join Date
    Feb 2014
    Location
    netherlands
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    the connecting table

    So I made a skills table:
    ID Name
    1 one
    2 two
    up until what I need.

    Then a Connection Table (JobSkills):
    ID Job ID Skill ID
    1 1 1
    2 1 2
    3 2 1
    etc

    I think Opening ID and Skill ID become Foreign Keys to their respective tables...
    Now the problem becomes: how to connect from the Jobs table (one row) to this connecting table (where there are multiple rows)?

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by bwakad View Post
    I think Opening ID and Skill ID become Foreign Keys to their respective tables...
    you mean job ID and yes they should

    and you don't need ID at all for that table

    as for connecting the jobs table, you don't have to, it's already connected by virtue of being the reference for a foreign key in the JobSkills table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Member
    Join Date
    Feb 2014
    Location
    netherlands
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    2NF

    I have the following table with a composite primary key:

    PK_companyID (gives reference to office/address)
    PK_jobID (when company makes a posting)
    DATE_published
    jobName
    jobFuncGrp
    branche
    education

    Now my question becomes: In second normal form, all non-key columns must depend on the entire primary key.

    jobName - dependent on both: without companyID there is no jobID and therefore no jobName
    jobFuncGrp - NOT dependent on both: has to do with the jobID
    branche - NOT dependent on both: has to do with the companyID.
    education - dependent on both - without companyID there is no jobID and therefore no education required.

    is this right???

  11. #11
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,871
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by bwakad View Post
    Now my question becomes: In second normal form, all non-key columns must depend on the entire primary key.

    is this right???
    Yes - so your next step is to split that table in three.

    PK_companyID
    branche

    PK_jobID
    jobFuncGrp

    PK_companyID
    PK_jobID
    jobName
    education

    You didn't say what DATE_published is dependent on but it belongs in whichever of the three tables where it is dependent on the entire key.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  12. #12
    SitePoint Member
    Join Date
    Feb 2014
    Location
    netherlands
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks!
    A question though.... in my case, I know what I want to display as fields(value) though PHP. And really just learning sql.
    But when starting designing a DB and tables, does one start with one-long-table displaying all fields after each other?
    I have a lot of fields regarding company details, job details and some other things. I guess it is all right to just put them all in one table being UNF?

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by bwakad View Post
    I guess it is all right to just put them all in one table being UNF?
    you guess wrong
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Member
    Join Date
    Feb 2014
    Location
    netherlands
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    oke, thanks for all the good answers. really helps me!

    I am left with a table for company details (a company can have sub offices, so that's why the ID came in handy):
    PK_companyID
    branche

    and these fields for which I thought to put them in the same table:
    name
    street
    zip
    city
    province
    phone

    On a side note, the job table has a city and province, but the company also.
    I think I need to make a separate table for these fields and reference them from the other two tables?
    City
    Province

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by bwakad View Post
    On a side note, the job table has a city and province, but the company also.
    I think I need to make a separate table for these fields and reference them from the other two tables?
    City
    Province
    no, this would be an error, in my opinion

    this would add a level of complexity that in most applications is just not warranted

    just because there is duplication does not automatically mean it's a bad thing, and doesn't imply the need for managing a completely new entity type

    leave city and province as simple attributes of their entitites
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Tags for this Thread

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
  •