Hierarchy/Many to Many/Temporal data - MIND BENDER

From: me <XDXaXrXcXyX_at_XSXiXtXeXwXaXrXeX.XcXoXmX>
Date: Sun, 17 Aug 2003 06:40:04 GMT
Message-ID: <8rF%a.178273$4UE.175003_at_news01.bloor.is.net.cable.rogers.com>

I'm finding this one tricky and hope someone can help.

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 Received on Sun Aug 17 2003 - 08:40:04 CEST