In a relationship model like this, you can track history by assigning a time
span for the relationship. For example, you would modify your
Employee_Positions table to contain from and to date fields:
Employee_Positions
Employee_ID
Position_ID
fromDate
toDate
You could then find the current position like this:
SELECT Employee_ID,
Position_ID
FROM Employee_Positions
WHERE getDate() BETWEEN fromDate AND isNull(toDate,getDate())
HTH,
Chris
> -----Original Message-----
> From: Dawson, Michael [mailto:[EMAIL PROTECTED]
> Sent: Thursday, April 14, 2005 3:49 PM
> To: CF-Talk
> Subject: RE: Database Normalization Question
>
>
> What happens you you have the same employee, but has been moved to a new
> position and you still need to keep the history?
>
> For example, last year I was a grunt. This year, I am a slave. How
> would you track that?
>
> -----Original Message-----
> From: Rick Faircloth [mailto:[EMAIL PROTECTED]
> Sent: Thursday, April 14, 2005 1:09 PM
> To: CF-Talk
> Subject: Database Normalization Question
>
> Greetings, all...
>
> Instead of two tables like this:
>
> Positions (Yes, I like plural table names :o)
>
> Position_ID (Primary Key)
> Position_Title
> Position_Description
> etc
>
> Employees
>
> Employee_ID (Primary Key)
> Position_ID (Relational Key)
> Employee_FirstName
> Employee_LastName
> etc
>
>
>
> I've seen many use examples of three tables, a third table which seems
> to be the way of creating relationships between tables.
> I just typically do it with two tables and what I always thought of as a
> "Foreign Key", which may not be the accurate term anyway. Here's a
> probably poor example of the three table scheme I've seen:
>
> Positions
>
> Position_ID (Primary Key)
> Position_Title
> Position_Description
> etc
>
> Employees
>
> Employee_ID (Primary Key)
> Employee_FirstName
> Employee_LastName
> etc
>
> Employee_Positions
>
> Employee_ID
> Position_ID
>
>
> It seems like the third table is used to tie the Positions table and
> Employees table together, but I don't see the benefit of creating that
> third table when I can just put the Position_ID in the Employees
> Table...
>
> This may be a poor example of what I'm talking about. I can't think of
> an exact example I've seen, but those of you who do this will know,
> probably, what I've referring to. I thought I've been doing correct
> normalization.
> Using the third table seems to cause the use of repeated data and more
> tables than the first example...so why is it done? What am I missing in
> my database design, which, of course, would determine how I have to code
> in CF and SQL...
>
> Rick
>
>
>
>
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Discover CFTicket - The leading ColdFusion Help Desk and Trouble
Ticket application
http://www.houseoffusion.com/banners/view.cfm?bannerid=48
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:203019
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
