RE: Database Normalization Question
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
RE: Database Normalization Question
Sounds good...thanks Kerry! Rick > -Original Message- > From: Kerry [mailto:[EMAIL PROTECTED] > Sent: Friday, April 15, 2005 6:25 AM > To: CF-Talk > Subject: RE: Database Normalization Question > > > just a small point, the 2 table system does allow for > one-to-many, the link > table is only required for many-to-many. > > position_id,position_name > 1,Helpdesk Analyst > > employee_id,position_id > 1,1 > 2,1 > 3,1 > 4,1 > > > Now we have one position, with 4 employees, one to many. > > In this situation, I would go for the link table, because its much more > flexible for the future, e.g. if the client changes their organisational > structure > > > position_id,position_name > 1,Helpdesk Analyst > 2,Desktop Tech > > > employee_id,position_id > 1,1 > 2,1 > 2,2 > > employee_id > 1 > 2 > 3 > 4 > > > -----Original Message- > From: Rick Faircloth [mailto:[EMAIL PROTECTED] > Sent: 14 April 2005 20:53 > To: CF-Talk > Subject: RE: Database Normalization Question > > > 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 > > > > ~| 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:203020 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
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 > > > > > > > ~| 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
RE: Database Normalization Question
just a small point, the 2 table system does allow for one-to-many, the link table is only required for many-to-many. position_id,position_name 1,Helpdesk Analyst employee_id,position_id 1,1 2,1 3,1 4,1 Now we have one position, with 4 employees, one to many. In this situation, I would go for the link table, because its much more flexible for the future, e.g. if the client changes their organisational structure position_id,position_name 1,Helpdesk Analyst 2,Desktop Tech employee_id,position_id 1,1 2,1 2,2 employee_id 1 2 3 4 -Original Message- From: Rick Faircloth [mailto:[EMAIL PROTECTED] Sent: 14 April 2005 20:53 To: CF-Talk Subject: RE: Database Normalization Question 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 ~| 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:203015 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
RE: Database Normalization Question
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
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: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
RE: Database Normalization Question
I've noticed that being done, too. Multiple-field Primary Keys... That'll really require some re-thinking of how I design databases and code for them...but I know I'll be better off once I make the transition. Thanks! Rick > -Original Message- > From: Chris Stoner [mailto:[EMAIL PROTECTED] > Sent: Thursday, April 14, 2005 2:56 PM > To: CF-Talk > Subject: Re: Database Normalization Question > > > You don't actually need the Employee_Position_ID, just make the > primary key > out of both the Position_id and Employee_ID columns, i.e.: > > Employee_Positions > Position_ID [pk][fk] > Employee_ID [pk][fk] > > > > > - Original Message - > From: "Rick Faircloth" <[EMAIL PROTECTED]> > To: "CF-Talk" > Sent: Thursday, April 14, 2005 2:37 PM > Subject: RE: Database Normalization Question > > > > Thanks, Chris & Jochem... > > > > Yes, I believe I understand...I've just never run into a situation > > that forced the usage of the third table for me...such as multiple > > positions for single employees...or perhaps I've just worked around > > it. Now I'll have to figure out how to change my queries to reflect > > the use of this third table... > > > > I guess I just run "regular" insert, add, delete queries on the > > Positions and Employees tables, then when the time comes > > to assign a position to an employee, I'd list the employees, > > select one to the get the Employee_ID, then use a drop down > > of Postions to insert the Employee_ID and Position_ID into > > the Employee_Positions table. > > > > I guess I'd need to add an "Employee_Position_ID" to the > > third table? So it would be: > > > > Employee_Postions > > > > Employee_Position_ID > >Position_ID > >Employee_ID > > > > Is this proper perspective? > > > > Rick > > > > > > > -Original Message- > > > From: Jochem van Dieten [mailto:[EMAIL PROTECTED] > > > Sent: Thursday, April 14, 2005 2:25 PM > > > To: CF-Talk > > > Subject: Re: Database Normalization Question > > > > > > > > > Rick Faircloth wrote: > > > > > > > > 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 > > > > > > > > > > > > > Employee_Positions > > > > > > > >Employee_ID > > > >Position_ID > > > > > > > > > > > > It seems like the third table is used to tie the Positions table and > > > > Employees table together, > > > > > > > This may be a poor example of what I'm talking about. > > > > > > It is an excellent example. Just answer the following questions > > > and it should all become clear :) > > > > > > How many positions can an employee have in your first example? > > > How many positions can an employee have in your second example? > > > How many positions can an employee have in your company? > > > > > > Jochem > > > > > > > > > > > ~| 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:202828 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
Re: Database Normalization Question
You don't actually need the Employee_Position_ID, just make the primary key out of both the Position_id and Employee_ID columns, i.e.: Employee_Positions Position_ID [pk][fk] Employee_ID [pk][fk] - Original Message - From: "Rick Faircloth" <[EMAIL PROTECTED]> To: "CF-Talk" Sent: Thursday, April 14, 2005 2:37 PM Subject: RE: Database Normalization Question > Thanks, Chris & Jochem... > > Yes, I believe I understand...I've just never run into a situation > that forced the usage of the third table for me...such as multiple > positions for single employees...or perhaps I've just worked around > it. Now I'll have to figure out how to change my queries to reflect > the use of this third table... > > I guess I just run "regular" insert, add, delete queries on the > Positions and Employees tables, then when the time comes > to assign a position to an employee, I'd list the employees, > select one to the get the Employee_ID, then use a drop down > of Postions to insert the Employee_ID and Position_ID into > the Employee_Positions table. > > I guess I'd need to add an "Employee_Position_ID" to the > third table? So it would be: > > Employee_Postions > >Employee_Position_ID >Position_ID >Employee_ID > > Is this proper perspective? > > Rick > > > > -Original Message- > > From: Jochem van Dieten [mailto:[EMAIL PROTECTED] > > Sent: Thursday, April 14, 2005 2:25 PM > > To: CF-Talk > > Subject: Re: Database Normalization Question > > > > > > Rick Faircloth wrote: > > > > > > 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 > > > > > > > > > Employee_Positions > > > > > >Employee_ID > > >Position_ID > > > > > > > > > It seems like the third table is used to tie the Positions table and > > > Employees table together, > > > > > This may be a poor example of what I'm talking about. > > > > It is an excellent example. Just answer the following questions > > and it should all become clear :) > > > > How many positions can an employee have in your first example? > > How many positions can an employee have in your second example? > > How many positions can an employee have in your company? > > > > Jochem > > > > > ~| 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:202825 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
RE: Database Normalization Question
Makes sense...thanks! Rick > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: Thursday, April 14, 2005 2:31 PM > To: CF-Talk > Subject: RE: Database Normalization Question > > > Rick > > What you are referring to is a link table often used when you > have a many to > many relationship, if you have a one to one relationship then there is no > need. > > > > > > > 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... > > > > If one employee can only have one position at one time then there is no > benefit, however if an employee has many positions or many roles then > employee positions would allow you to retrieve all the positions for a > particular employee without having a repeating group or redundant data in > your employee table (i.e. multiple rows for the same employee but with > different positions.) > > HTH > > K > > > > > ~| 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:202822 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
RE: Database Normalization Question
Thanks, Chris & Jochem... Yes, I believe I understand...I've just never run into a situation that forced the usage of the third table for me...such as multiple positions for single employees...or perhaps I've just worked around it. Now I'll have to figure out how to change my queries to reflect the use of this third table... I guess I just run "regular" insert, add, delete queries on the Positions and Employees tables, then when the time comes to assign a position to an employee, I'd list the employees, select one to the get the Employee_ID, then use a drop down of Postions to insert the Employee_ID and Position_ID into the Employee_Positions table. I guess I'd need to add an "Employee_Position_ID" to the third table? So it would be: Employee_Postions Employee_Position_ID Position_ID Employee_ID Is this proper perspective? Rick > -Original Message- > From: Jochem van Dieten [mailto:[EMAIL PROTECTED] > Sent: Thursday, April 14, 2005 2:25 PM > To: CF-Talk > Subject: Re: Database Normalization Question > > > Rick Faircloth wrote: > > > > 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 > > > > > Employee_Positions > > > >Employee_ID > >Position_ID > > > > > > It seems like the third table is used to tie the Positions table and > > Employees table together, > > > This may be a poor example of what I'm talking about. > > It is an excellent example. Just answer the following questions > and it should all become clear :) > > How many positions can an employee have in your first example? > How many positions can an employee have in your second example? > How many positions can an employee have in your company? > > Jochem > ~| 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:202820 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
RE: Database Normalization Question
Rick What you are referring to is a link table often used when you have a many to many relationship, if you have a one to one relationship then there is no need. > > > 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... > If one employee can only have one position at one time then there is no benefit, however if an employee has many positions or many roles then employee positions would allow you to retrieve all the positions for a particular employee without having a repeating group or redundant data in your employee table (i.e. multiple rows for the same employee but with different positions.) HTH K ~| 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:202814 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
Re: Database Normalization Question
Rick Faircloth wrote: > > 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 > Employee_Positions > >Employee_ID >Position_ID > > > It seems like the third table is used to tie the Positions table and > Employees table together, > This may be a poor example of what I'm talking about. It is an excellent example. Just answer the following questions and it should all become clear :) How many positions can an employee have in your first example? How many positions can an employee have in your second example? How many positions can an employee have in your company? Jochem ~| 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:202813 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
Re: Database Normalization Question
The third table allows for a many to many relationship. So a person can have more then one position in your example. - Original Message - From: "Rick Faircloth" <[EMAIL PROTECTED]> To: "CF-Talk" Sent: Thursday, April 14, 2005 2:08 PM 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:202811 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
