Normalization

[lmcc007]

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?

 

[lmcc007]

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.

 

[lmcc007]

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 structure:

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

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?

 

[lmcc007]

Do I need a code or something?

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

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 structure:

[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?

 

[lmcc007]

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

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 structure:

[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?

 

[lmcc007]

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

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.

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?

 

[lmcc007]

http://www.box.net/shared/0hedzz7ixh

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.

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?

 

[lmcc007]

I've been studying MS Inside Out 2000, 2003, and 2007. And, it suggests
doing it on paper first. I haven't figured out the flow chart part, but
before I was laid off I would notice the programmers would do and use
flowcharts.

Wow, you put a lot of forethought into this, not building on-the-fly. When
you asked if could upload database I had thought would be actual project to
test run. Your summary positions the company as parent, 'For example, if
Shell Oil is deleted, I want all the events for Shell Oil deleted as well'.
You will not have more than one job per company? I see as the primary purpose
is to track job apps and all data contributes info to that effort, so the job
record is parent to events, thus recommendation for table with one record per
job. Really no need to delete records, use queries to filter output. But just
one opinion and am not an expert, just another user who learned because
project thrown at me at work and then another and another and another....

http://www.box.net/shared/0hedzz7ixh

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

[quoted text clipped - 14 lines]

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

 

[lmcc007]

No, there may be several jobs per company. For example I have applied with
Shell Oil several times:

Date EventType JobTitle Notes
1/1/08 SubResume Administrator
1/1/08 Email recd Administrator Confirmation ltr
2/1/08 SubResume Admin. I
2/1/08 Email recd Admin. I Confirmation ltr
5/5/08 SubResume Admin. II

I think I have worked at this too much I am confusing myself. But I have a
JobTitle table that is a list of job titles.

Now, I am thinking about doing a JobTitleInfo table that gives information
about each job I applied for. That's where I am confused on whether I should
do this.