Well...if it was a one-to-one relationship always, and I had a two-table db design, it would work, because I could simply update the employee's profile and use a drop-down to reassign the position.
But, if a one-to-many relationship could exist in the organization, I would need that three table, double-field primary key design... Right? Rick > -----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:202848 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
