From: | Andrew Gould <andrewgould(at)yahoo(dot)com> |
---|---|
To: | lockhart(at)fourpalms(dot)org, Joshua Adam Ginsberg <rainman(at)owlnet(dot)rice(dot)edu> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Query help... |
Date: | 2001-08-11 17:06:51 |
Message-ID: | 20010811170651.29958.qmail@web13406.mail.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)fourpalms(dot)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(at)postgresql(dot)org
__________________________________________________
Do You Yahoo!?
Send instant messages & get email alerts with Yahoo! Messenger.
http://im.yahoo.com/
From | Date | Subject | |
---|---|---|---|
Next Message | Stephan Szabo | 2001-08-11 17:08:00 | Re: INT8 in Postgres |
Previous Message | Andrew Gould | 2001-08-11 16:59:26 | Re: Query help... |