A Good DB design? Having problem updating 2 recordset with same ID
sree'z
I have created a DB which holds information on employee details.
My design is as follows:-
=========================
Employee Tbl
EmpID(pk)
FName
LName
Emp_Info Tbl
Emp_ID(fk)
Dept_ID(fk)
Title
Phone
Email
Department Tbl
DeptID(pk)
Name
Address
Phone
Fax
The reason why i structured my db this way is because I have employees working in 2 different departments at the sametime and to accomodate that I have created the Emp_Info tbl which can hold the particular information if the employee is working in 2 department.
I have got everything working fine except when I want to update. Like for an example In Emp_Info TBL
******************************
Emp_ID
92
92
Dept_ID
10
12
Title
Executive
DB Support
Phone
399-5218
399-5524
Email
da...@iie.com
da...@iie.com
*******************************
What happens is... when I want to do an update.. I am not sure how to specifically do an update to a particular record. Right now with the update statement I have .. it is updating both the records with the same information. I guess it's looking for the emp_id number and if it finds that there is 2 emp_id with the same value... it updates bot the record.
My question is, is this a good DB design.. I am using a coldfusion 5 for this project and have been cracking my head on this for 2 weeks. Any help is very much appreciated. Thanks
lot..
***************************************
This is how my update statement looks like:-
<CFQUERY name="Update_Employee" datasource="LE2">
UPDATE Employee
SET Fname ='#Form.Fname#',
Lname ='#Form.Lname#'
WHERE EmpID = #Form.ID#
</CFQUERY>
<CFQUERY name="Update_Emp_Info" datasource="LE2">
UPDATE Emp_Info
SET Dept_ID = #Form.Dept#,
Title ='#Form.Title#',
Phone = '#Form.Phone#',
Email ='#Form.email#',
WHERE Emp_ID = #Form.ID#
</CFQUERY>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
I have had people help me this far and I really appreciate all the help but I am still confused how to deal with this problem. Any of you people had any similar problem or designed a similar db before? Thanks again in advance for the help.
sree'z
It sounds like you also need the Dept_ID in your form. That way when you do your 2nd query to update the Emp_Info table you can change you WHERE statement from WHERE Emp_ID = #Form.ID# to WHERE Emp_ID = #Form.ID# AND Dept_ID = #Form.DeptID#
This will only update records where both Emp_ID and Dept_ID match. If you are allowing your employees to update all of their positions at once you will have to come up with the logic for that. An idea might be that if the employee has more than one department to make them choose which one they want to update before taking them to your form, then passing that Dept_ID into the form.
Hope this helps
*********************************************************
Hi rouwd,
I have tried the method you have suggested which is WHERE Emp_ID = #Form.ID# AND Dept_ID = #Form.DeptID# but it still updates both the recordset in the Emp_Info Tbl. I like your second suggestion.. but I did not get what exactly you mean.. could please tell me more about it. I really appreciate your help. Thanks
rouwd
<cfquery datasource="LE2" name="getDept">
Select Dept_ID, Title from Emp_Info where Emp_ID = #EmployeeID#
</cfquery>
#EmployeeID# should be available after the user logs in
<cfif getDept.RecordCount gt 1>
Please choose a department to update.<br>
<cfoutput query="getDept">
<a href="updateProfile.cfm?Dept_ID=#Dept_ID#">#Title#</a><br>
</cfoutput>
<cfelseif getDept.RecordCount eq 1>
<cflocation url="updateProfile.cfm?Dept_ID=#getDept.Dept_ID#" addtoken="Yes">
<cfelse>
take them to your registration page to add a department.
</cfif>
Notice that if the user only has 1 department they will automatically be taken to the update page. Only those with multiple departments will have to take the extra step.
Your update page would then read the URL variable URL.Dept_ID and retrieve that information from the Emp_Info table to be displayed. I noticed that you had a form field called Dept. I assume that field is the Dept_ID and that it is hidden or at least read only. If you allow users to change a key value you won't be able to update a record because it might not exist. You can use that in your Where statement though. Here is how I would write the update statement.
<CFQUERY name="Update_Emp_Info" datasource="LE2">
UPDATE Emp_Info
SET Title ='#Form.Title#',
Phone = '#Form.Phone#',
Email ='#Form.email#',
WHERE Emp_ID = #Form.Emp_ID# and Dept_ID = #Form.Dept#
</CFQUERY>
I didn't test the code, but it should be pretty close.
sree'z
There is a small prolem here.. currently i am only allowing the admin to login and change the details of the employees. Since i told you before i have 3 tables and emp_info tbl holds the information of employees working in 2 different departments. I can make the employee details page which hold the information of Fname, lname title, dept, phone and etc to show up and create a edit link rite next to it which will bring me to another page where the admin will be able to edit the information shown in the employee details page.
Want i want to have now is to be able to add another department link from the employee details page which will open up another page to add the employee to a second department with all the other info.
Once this is inserted, it should create another record in my emp-info tbl with the same emp_ID but with different dept_ID.
When I want to edit the particular employee, the edit page should be able to locate if the employee is associated with 2 departments and pull all the info for edit and at the same time should be able to update each records individually.
***************
This is what i am looking for.. do you think i shold have a surrogate key for my emp_info table? since the tbl only holds 2 foreign keys. I hope you understand me.. I am still new to coldfusion and this is my first project .. i am trying my best with help of you guys. I really appreciate all your help. Thanks