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…
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
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?:
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.
So I made a skills table:
[TABLE=“width: 300”]
[TR]
[TD]ID
[/TD]
[TD]Name
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]one
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]two
[/TD]
[TD][/TD]
[/TR]
[/TABLE]
up until what I need.
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)?
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.
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?
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
