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:202841 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
