I like Thomas's idea better than the one I just
posted.
If jobs are one-time projects, I would also create a
unique index on staff_history(staff_id, job_id). This
would help keep the data clean. (This would not work
if jobs are employment positions or projects that an
employee can leave and return to.)
Andrew Gould
--- Thomas Lockhart <lockhart@fourpalms.org> wrote:
> > I've got three tables that I'm working with... the
> first is a table of
> > staff members... firstname, lastname, staffid...
> nothing tough... the
> > second is a table of jobs... job title, jobid...
> nothing tough... the
> > third is a history of job assignments... it's got
> a staffid, a jobid, a
> > timestamp, and a field to denote whether the job
> was added or dropped...
> > this third table exists because it is useful in
> this project to be able
> > to look at a staff member's job history...
> ...
> > Any suggestions?
>
> Perhaps not a helpful one... but I would be inclined
> to reorganize that
> third table to have a "start date" *and* a "stop
> date" field, and ditch
> the "dropped" boolean:
>
> o it would keep you from having to figure out how to
> associate two
> entries in the same table with the same job (the
> added and dropped
> rows). How would you currently prevent a job from
> being entered as
> "dropped" without a corresponding "added" row?
>
> o it would make it easier to do the query you are
> asking about; to tell
> whether someone has an active job, just select on a
> date -- like 'today'
> -- between the start and stop dates.
>
> o it may better match reality; jobs have a duration
> so represent that
> explicitly.
>
> hth
>
> - Thomas
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to
majordomo@postgresql.org
__________________________________________________
Do You Yahoo!?
Send instant messages & get email alerts with Yahoo! Messenger.
http://im.yahoo.com/
