Great! Thanks, Chris Rick
> -----Original Message----- > From: Chris Terrebonne [mailto:[EMAIL PROTECTED] > Sent: Friday, April 15, 2005 8:04 AM > To: CF-Talk > Subject: RE: Database Normalization Question > > > 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 > > > > > > > > > > > > > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:203021 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=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
