Re: Hierarchy/Many to Many/Temporal data - MIND BENDER

Is there anyone here who can enlighten me on how to produce the queries to support the three reports in my problem?

If you don't like field names or anything about the suggested table structure, just make the changes in your suggested solution.

I'm betting its possible but I can't find someone to do it so unless someone comes forward, I'll be writing procedural code that handles the queries rather than plain select statements.

"me" <XDXaXrXcXyX_at_XSXiXtXeXwXaXrXeX.XcXoXmX> wrote in message news: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 Thu Aug 21 2003 - 00:19:09 CEST