Normalization

I am trying to create a job tracking database. I have the company
information in a separate table. And I created an event table to store all
the activities. The following are the fields in the event table:

EventID
CompanyID
ContactID
Date
BeginTime
EndTime
EventTypeID
ResumeSubmittedHowID
ReferencesSubmitted
LeadSourceID
JobTypeID
JobTitleID
Salary
JobPostingAttached
NotesHistory

EventID is created for each event. For Example:

EventID Company Date Event JobType JobTitle RefSub
1 Shell Oil 1/1/08 Uploaded Res. Perm. Admin. No
2 Shell Oil 1/1/08 Recd conf ltr
3 Shell Oil 5/5/08 Uploaded Res. Perm. Admin. II No
4 Shell Oil 5/5/08 Job not avail Admin. II

Do you think I need to put job information in a separate table for
normalization? Or is it okay in this table?

I think yes: job info in a table, events in a table, company info in a table.

lmcc007 wrote:
I am trying to create a job tracking database. I have the company
information in a separate table. And I created an event table to store all
the activities. The following are the fields in the event table:

EventID
CompanyID
ContactID
Date
BeginTime
EndTime
EventTypeID
ResumeSubmittedHowID
ReferencesSubmitted
LeadSourceID
JobTypeID
JobTitleID
Salary
JobPostingAttached
NotesHistory

EventID is created for each event. For Example:

EventID Company Date Event JobType JobTitle RefSub
1 Shell Oil 1/1/08 Uploaded Res. Perm. Admin. No
2 Shell Oil 1/1/08 Recd conf ltr
3 Shell Oil 5/5/08 Uploaded Res. Perm. Admin. II No
4 Shell Oil 5/5/08 Job not avail Admin. II

Do you think I need to put job information in a separate table for
normalization? Or is it okay in this table?

--
Message posted via http://www.accessmonster.com

I thought about it some more and it still seems that job info should be in
this table because I am basically asking questions: HowResumeSubmitted,
ReferencesSubmitted, LeadSource, and JobType so I can create a report with
this information. Each job I apply for probably will have different job
descriptions.

Maybe I am off and a little lost, but all I am trying to do is keep track of
each activity for a company. And, I need questions asked about the resumes I
send out so I can fill out my job-search log.


"June7 via AccessMonster.com" wrote:

I think yes: job info in a table, events in a table, company info in a table.

lmcc007 wrote:
I am trying to create a job tracking database. I have the company
information in a separate table. And I created an event table to store all
the activities. The following are the fields in the event table:

EventID
CompanyID
ContactID
Date
BeginTime
EndTime
EventTypeID
ResumeSubmittedHowID
ReferencesSubmitted
LeadSourceID
JobTypeID
JobTitleID
Salary
JobPostingAttached
NotesHistory

EventID is created for each event. For Example:

EventID Company Date Event JobType JobTitle RefSub
1 Shell Oil 1/1/08 Uploaded Res. Perm. Admin. No
2 Shell Oil 1/1/08 Recd conf ltr
3 Shell Oil 5/5/08 Uploaded Res. Perm. Admin. II No
4 Shell Oil 5/5/08 Job not avail Admin. II

Do you think I need to put job information in a separate table for
normalization? Or is it okay in this table?

--
Message posted via http://www.accessmonster.com

Based on your description, I can envision the following structu

Assumes only one address possible for each company.
tblCompany
CompanyID
CompanyName
CompanyAddress
CompanyCity
CompanyState
CompanyZip

This table suggested if possible to have more than one contact at a company,
if not could incorporate in Company table
tblContacts
ContactID
CompanyID
ContactFirstName
ContactLastName

One record per Job
tblJobs
JobID
CompanyID
ContactID (only if separate table of contacts maintained)
JobTitle
JobSalary
BeginTime
EndTime
LeadSourceID
JobPostingAttached
ResumeSubmittedHow (If limited methods – less than 5? – suggest a value list
of choices, otherwise use a table as source)
ReferencesSubmitted (Yes/No field? Because suggest related references be kept
in another table)
NotesHistory

Presume each job can have more than one event and want to maintain activity
history. If don’t care about history, just most recent, then could be just a
field in tblJobs called Status which would be modified at each stage and the
event type would be the status, also modify EventDate field.
tblEvents
EventID
EventType (if limited types – less than 5? – suggest a value list of choices,
otherwise use a table as source)
JobID
EventDate

tblLeadSources
LeadSourceID
SourceLastName
SourceFirstName
Address?, phone?

tblReferences
ReferenceID
ReferenceLastName
ReferenceFirstName
ReferencePhone
ReferenceAddress
ReferenceCity
ReferenceState
ReferenceZip

tblJobReferences
JobID
ReferenceID


lmcc007 wrote:
I thought about it some more and it still seems that job info should be in
this table because I am basically asking questions: HowResumeSubmitted,
ReferencesSubmitted, LeadSource, and JobType so I can create a report with
this information. Each job I apply for probably will have different job
descriptions.

Maybe I am off and a little lost, but all I am trying to do is keep track of
each activity for a company. And, I need questions asked about the resumes I
send out so I can fill out my job-search log.

I think yes: job info in a table, events in a table, company info in a table.

[quoted text clipped - 28 lines]
Do you think I need to put job information in a separate table for
normalization? Or is it okay in this table?

--
Message posted via http://www.accessmonster.com

My structure is like you describe (separate tables, etc.) except when I get
to events. That's my dilemma.

The events table track any activity--such as, sent resume, called about job,
received confirmation e-mail.

The problem I am having trouble with is JobTitle. I have a tblJobTitles
table so I will not have to repeat Administrator over and over. Sometimes
the advertisement may have a description and sometimes not. Many times I
won't receive a response to the resume sent so there will not be any details
to write about (details meaning salary and so on).

I guess I am thinking that JobTitle info is not a separate entity from
events because without applying for a job or calling a company about a job
there will be no event/activity.

Right now my mind is a little off. Maybe because I am trying so hard to
normalize everything.

Is there a way to upload a database, so I can show exactly what I am talking
about?


"June7 via AccessMonster.com" wrote:

Based on your description, I can envision the following structu

Assumes only one address possible for each company.
tblCompany
CompanyID
CompanyName
CompanyAddress
CompanyCity
CompanyState
CompanyZip

This table suggested if possible to have more than one contact at a company,
if not could incorporate in Company table
tblContacts
ContactID
CompanyID
ContactFirstName
ContactLastName

One record per Job
tblJobs
JobID
CompanyID
ContactID (only if separate table of contacts maintained)
JobTitle
JobSalary
BeginTime
EndTime
LeadSourceID
JobPostingAttached
ResumeSubmittedHow (If limited methods – less than 5? – suggest a value list
of choices, otherwise use a table as source)
ReferencesSubmitted (Yes/No field? Because suggest related references be kept
in another table)
NotesHistory

Presume each job can have more than one event and want to maintain activity
history. If don’t care about history, just most recent, then could be just a
field in tblJobs called Status which would be modified at each stage and the
event type would be the status, also modify EventDate field.
tblEvents
EventID
EventType (if limited types – less than 5? – suggest a value list of choices,
otherwise use a table as source)
JobID
EventDate

tblLeadSources
LeadSourceID
SourceLastName
SourceFirstName
Address?, phone?

tblReferences
ReferenceID
ReferenceLastName
ReferenceFirstName
ReferencePhone
ReferenceAddress
ReferenceCity
ReferenceState
ReferenceZip

tblJobReferences
JobID
ReferenceID


lmcc007 wrote:
I thought about it some more and it still seems that job info should be in
this table because I am basically asking questions: HowResumeSubmitted,
ReferencesSubmitted, LeadSource, and JobType so I can create a report with
this information. Each job I apply for probably will have different job
descriptions.

Maybe I am off and a little lost, but all I am trying to do is keep track of
each activity for a company. And, I need questions asked about the resumes I
send out so I can fill out my job-search log.

I think yes: job info in a table, events in a table, company info in a table.

[quoted text clipped - 28 lines]
Do you think I need to put job information in a separate table for
normalization? Or is it okay in this table?

--
Message posted via http://www.accessmonster.com

If you want to drop your project to this site, will be happy to look at:
http://www.box.net/shared/0mfqudj3nz

lmcc007 wrote:
My structure is like you describe (separate tables, etc.) except when I get
to events. That's my dilemma.

The events table track any activity--such as, sent resume, called about job,
received confirmation e-mail.

The problem I am having trouble with is JobTitle. I have a tblJobTitles
table so I will not have to repeat Administrator over and over. Sometimes
the advertisement may have a description and sometimes not. Many times I
won't receive a response to the resume sent so there will not be any details
to write about (details meaning salary and so on).

I guess I am thinking that JobTitle info is not a separate entity from
events because without applying for a job or calling a company about a job
there will be no event/activity.

Right now my mind is a little off. Maybe because I am trying so hard to
normalize everything.

Is there a way to upload a database, so I can show exactly what I am talking
about?

Based on your description, I can envision the following structu

[quoted text clipped - 78 lines]
Do you think I need to put job information in a separate table for
normalization? Or is it okay in this table?

--
Message posted via http://www.accessmonster.com

Do I need a code or something?

"June7 via AccessMonster.com" wrote:

If you want to drop your project to this site, will be happy to look at:
http://www.box.net/shared/0mfqudj3nz

lmcc007 wrote:
My structure is like you describe (separate tables, etc.) except when I get
to events. That's my dilemma.

The events table track any activity--such as, sent resume, called about job,
received confirmation e-mail.

The problem I am having trouble with is JobTitle. I have a tblJobTitles
table so I will not have to repeat Administrator over and over. Sometimes
the advertisement may have a description and sometimes not. Many times I
won't receive a response to the resume sent so there will not be any details
to write about (details meaning salary and so on).

I guess I am thinking that JobTitle info is not a separate entity from
events because without applying for a job or calling a company about a job
there will be no event/activity.

Right now my mind is a little off. Maybe because I am trying so hard to
normalize everything.

Is there a way to upload a database, so I can show exactly what I am talking
about?

Based on your description, I can envision the following structu

[quoted text clipped - 78 lines]
Do you think I need to put job information in a separate table for
normalization? Or is it okay in this table?

--
Message posted via http://www.accessmonster.com

I sign up, but how do I send it you?

"June7 via AccessMonster.com" wrote:

If you want to drop your project to this site, will be happy to look at:
http://www.box.net/shared/0mfqudj3nz

lmcc007 wrote:
My structure is like you describe (separate tables, etc.) except when I get
to events. That's my dilemma.

The events table track any activity--such as, sent resume, called about job,
received confirmation e-mail.

The problem I am having trouble with is JobTitle. I have a tblJobTitles
table so I will not have to repeat Administrator over and over. Sometimes
the advertisement may have a description and sometimes not. Many times I
won't receive a response to the resume sent so there will not be any details
to write about (details meaning salary and so on).

I guess I am thinking that JobTitle info is not a separate entity from
events because without applying for a job or calling a company about a job
there will be no event/activity.

Right now my mind is a little off. Maybe because I am trying so hard to
normalize everything.

Is there a way to upload a database, so I can show exactly what I am talking
about?

Based on your description, I can envision the following structu

[quoted text clipped - 78 lines]
Do you think I need to put job information in a separate table for
normalization? Or is it okay in this table?

--
Message posted via http://www.accessmonster.com

Click on the link in my post which should take you to my folder on the Box.
net site and you should see an Upload button. When you click it should open
dialog box that allows you to browse to your file and select it for upload.
If my Share box won't work then post link to file in your box and I will
download from there. If you signed up on the Box.net site then on the right
side of the folder or file is a box that says 'Shared'. Click on it and will
open a frame that shows the link url. Select and copy the url string then
paste into post here. Be sure to copy the link to the file for me to download,
not the folder. Whenever a file is loaded up or down the box owner gets and e-
mail.

lmcc007 wrote:
I sign up, but how do I send it you?

If you want to drop your project to this site, will be happy to look at:
http://www.box.net/shared/0mfqudj3nz
[quoted text clipped - 26 lines]
Do you think I need to put job information in a separate table for
normalization? Or is it okay in this table?

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200907/1

http://www.box.net/files#0:f:29458238

"June7 via AccessMonster.com" wrote:

Click on the link in my post which should take you to my folder on the Box.
net site and you should see an Upload button. When you click it should open
dialog box that allows you to browse to your file and select it for upload.
If my Share box won't work then post link to file in your box and I will
download from there. If you signed up on the Box.net site then on the right
side of the folder or file is a box that says 'Shared'. Click on it and will
open a frame that shows the link url. Select and copy the url string then
paste into post here. Be sure to copy the link to the file for me to download,
not the folder. Whenever a file is loaded up or down the box owner gets and e-
mail.

lmcc007 wrote:
I sign up, but how do I send it you?

If you want to drop your project to this site, will be happy to look at:
http://www.box.net/shared/0mfqudj3nz
[quoted text clipped - 26 lines]
Do you think I need to put job information in a separate table for
normalization? Or is it okay in this table?

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200907/1