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