bwakad — 2014-03-02T17:17:21-05:00 — #1
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
r937 — 2014-03-02T17:31:12-05:00 — #2
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?
bwakad — 2014-03-02T18:25:04-05:00 — #3
Job Openings would produce a list of ALL companies;
Company Openings would produce a list of Openings AT this company in question
r937 — 2014-03-02T18:40:04-05:00 — #4
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
bwakad — 2014-03-02T19:03:23-05:00 — #5
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?
felgall — 2014-03-02T20:25:26-05:00 — #6
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.
r937 — 2014-03-02T21:40:04-05:00 — #7
no, skills don't relate to companies, they relate to jobs
like this --
22 acme inc
24 delux corp
25 giant co
3124 22 programmer
3125 22 programmer/analyst
3166 24 ui developer
bwakad — 2014-03-03T13:59:06-05:00 — #8
So I made a skills table:
up until what I need.
Then a Connection Table (JobSkills):
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)?
r937 — 2014-03-03T15:27:58-05:00 — #9
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
bwakad — 2014-03-05T11:59:48-05:00 — #10
I have the following table with a composite primary key:
PK_companyID (gives reference to office/address)
PK_jobID (when company makes a posting)
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???
felgall — 2014-03-05T13:30:06-05:00 — #11
Yes - so your next step is to split that table in three.
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.
bwakad — 2014-03-05T16:06:12-05:00 — #12
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?
r937 — 2014-03-05T17:14:40-05:00 — #13
you guess wrong
bwakad — 2014-03-05T18:05:16-05:00 — #14
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):
and these fields for which I thought to put them in the same table:
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?
r937 — 2014-03-05T19:39:56-05:00 — #15
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