|
I've posted this in the microsoft news group but just noticed the comp
newsgroups. What's the difference anyways?
This one is a tricky one so I'm interested in seeing what all you gurus have
to say.
I'm trying to put a resume into a database. There are only three reports
(see below).
I suspect that the best structure for a resume database is as follows:
Company
CompanyID Autonumber Primary Key
CompanyName Text
CompanyDetails Text
Skill
SkillID Autonumber Primary Key
SkillParentID Number
SkillName Text
Experience
ExperienceID Autonumber Primary Key
CompanyID number
ExperienceStart Date/Time
ExperienceStop Date/Time
Description Text
Detail Memo
Keywords Memo
ExperienceSkill
ExperienceSkillID Autonumber Primary Key
ExperienceID Number
SkillID Number
Percent Number
So each Experience is linked to a number of skills through the
ExperienceSkill Junction table. Also, the Skills are a hierarchy using the
adjacency set model. (I know some people will tell me to use the nested set
model but I'm only interested in it if the code for maintaining it is
provided and it can be shown how to produce the reports).
I like surrogate keys since this will likely be deployed through a Web
application but I'll listen to anyone who can cook up a data structure and
provide the queries to render the reports.
I suspect that the above data schema is ok but writting the queries has
proven to be a challenge.
Ideally, i'd like to figure out a structure so that the queries are just a
bunch of SELECT queries with no procedural code.
///////////////////////
Report 1: Skill Summary (by node)
///////////////////////
This report is trickier than it looks since the total (T1) for each section
are not plain totals due to the overlapping of assignments. Example, if the
candidate used FoxPro 2.5 for 1m and 2.6 for 1m, that doesn't mean they have
2m of FoxPro since the two assignments could have had the same begin and end
dates. T2 is just a plain total.
Report 1A (From Root):
T1 T2
Management 8m 14m
Project Management 5m 5m
Technical Leadership 3m 3m
Training/Mentoring 2m 6m
Programming 8m 23m
FoxPro 7m 17m
FoxPro 2.0 DOS 1m 2m
FoxPro 2.5 DOS 1m 2m
FoxPro 2.6 DOS 2m 2m
Visual FoxPro 3.0 4m 5m
Visual FoxPro 6.0 5m 6m
Visual Basic 2m 2m
Visual Basic 3.0 1m 1m
Visual Basic 6.0 1m 1m
Access 2m 4m
Report 1B (From FoxPro):
FoxPro 7m 17m
FoxPro 2.0 DOS 1m 2m
FoxPro 2.5 DOS 1m 2m
FoxPro 2.6 DOS 2m 2m
Visual FoxPro 3.0 4m 5m
Visual FoxPro 6.0 5m 6m
//////////End Report 1
///////////////////////
Report 2: Experience Summary (by node and all else)
///////////////////////
This report is a list of work experiences that fall underneath one tree
branch. The tricky part here is that the work experiences have leaf skill
attached to them. That is FoxPro 2.0 DOS can be assigned to an experience
but not FoxPro (the parent of FoxPro 2.0 DOS). But we want to show the
parent records of the skill anyways.
Report 2A (From FoxPro): This will list all the experiences that have a
FoxPro skill.
Company: Banana Company
Position: Programmer Analyst
Date: Jan 1, 1995-Feb 1, 1995
Desription: Jumped around for a while
Skills:
Programming, FoxPro, FoxPro 2.0 DOS, FoxPro 2.5 DOS
Management, Training/Mentoring
Company: Apples Company
Position: Programmer Analyst
Date: Jan 1, 1995-Feb 1, 1995
Desription: Ate beans and weiners.
Skills:
Programming, Access
Management, Training/Mentoring
....
....
....
Report 2B (All else but FoxPro): This is all the experences that are not in
the above report.
{same as above but just different entries come forward}
//////////End Report 2
///////////////////////
Report 3: Timeline by (node)
///////////////////////
This is a list of dates over which a skill was used. The tricky part here is
that the work experiences overlap so we have to merge many of the time
periods. We don't want to show any overlapping dates on this report.
Report 3A (from FoxPro): We need to show the dates for FoxPro and the skill
under FoxPro in one report.
Jan 1, 1995 to Feb 1, 1995
March 15, 1996 to April 25, 1996
//////////End Report 3
Thanks for sharing your ideas about this database problem!
If you wish to contact me directly, take the X's away from my email address.
Darcy
| 
