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

Originally posted by Me

You certainly do! What is wrong with using (ExperienceID,SkillID) as
the primary key for ExperienceSkill rather than adding the surrogate
ExperienceSkillID? You surely want them to be unique anyway.


--
Posted via http://dbforums.com

They are unique in the simplified example that I posted.

In the future if there is a date in that table then you will be able to have
the same skill assigned to the same experience twice. Each with a different
date.

With a surrogate key, the programming is easy:

example asp script using surrogate key:
exp.asp?action=edit&key=45

I just use "select * from company where companiID = 45" in the above script.

Or an application can pass a value to a form property in Access or VB.

Example:
myform.key=45

What do you propose would be passed to the routine for editing the data
after using multiple fields as a key?

Darcy










"andrewst" <member14183 (AT) dbforums (DOT) com> wrote

Originally posted by Me

... in which case I would add the date column to the primary key. That
makes 3 columns in the primary key, so it does make a surrogate seem
attractive for some purposes. You probably still want a UNIQUE
constraint though.



Originally posted by Me

Well, I don't use Access or VB much myself, so I may get this wrong, but
what about:

myform.ExperienceID=11

myform.SkillID=22



Surrogate keys are fine in their place, but I don't really like the
thought of the application tail wagging the database dog - it really
should be possible to handle a composite key in the application, if a
composite key is the logical choice for the table.


--
Posted via http://dbforums.com

and it can be shown how to produce the reports). <<

You can Google the nested sets code easily, but I would tell you to
design a hierarchical encoding scheme for the skills, or see if one
exists that you can steal. Ever use Dewey Decimal at the library?

Em Mon, 18 Aug 2003 12:17:56 +0000, me escreveu:

But you have no candidate keys to ensure uniqueness.


--
_ Leandro Guimarães Faria Corsetti Dutra +41 (21) 648 11 34
/ \ http://br.geocities.com./lgcdutra/ +41 (78) 778 11 34
\ / Responda Ã* lista, não a mim diretamente! +55 (11) 5686 2219
/ \ Dê nota se ajudei: http://svcs.affero.net/rm.php?r=leandro

I guess you figured out who "some people" was in my message.

I like the nested set model but I find it more difficult to work with and
I'm trying to keep this one simple and focus on getting the queries to
support the three reports done. If we can do that then yippie!

On the skill representation, are you suggesting to use some sort of path
field to represent the skill hierarchy? Are there any good articles around
that might suggest how I represent the skills with a Dewey system?

Darcy


"--CELKO--" <joe.celko (AT) northface (DOT) edu> wrote

articles around that might suggest how I represent the skills with a
Dewey system? <<

I have a chapter in DATA & DATABASES on the design of encoding
schemes. I don't know if Robert Half or some other big employment
agency already has such a code, but it will not be that bad to create
for yourself: 1000's = programming languages, 2000's = operating
systems, etc. Geek skills are a relatively small domain.

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 (DOT) XcXoXmX> wrote

Hi Darcy,

You seem to be posting in the wrong newsgroup. You want
comp.databases.reports

Regards,
Bob

"me" <XDXaXrXcXyX (AT) XSXiXtXeXwXaXrXeX (DOT) XcXoXmX> wrote