July 19, 2012 at 12:37 pm
dtopicdragovic (7/19/2012)
I thought more in case when user deletes for example info in some specific field. Since I update just changed info into NeedApproval.Table, I wondered what I will do with deleted fields. How will I keep track of what has been deleted. I also have few other tables that are involved in this whole thing and user might want to delete a whole row in some cases.I think that I should create stage tables for Delete operations too. ex. Delete_Contact that would function in similar way as NeedUpdate.Table, but just for deleted info.
Thanks
DD
Well you can't delete only a column, a delete by definition is the entire row. In the case of what you are doing a column could become NULL. That is what the case statement I presented would handle.
I would strongly urge you not to create a new table for deletes. Suddenly you are going to have a table for delete, insert, update. How would you handle rows from each of those tables? Which one takes precedence? If anything, you should add an OperationType column as a char(1) with values of 'A', 'D', 'U' (Add, Update, Delete).
I have to be honest I don't quite understand the point of what you are doing entirely. You want to allow the users to change their data. The new changes will be shown to the user. But you have a background approval process. What good is the approval if you show the new data anyway? The process you are describing sounds overly complicated with zero return for you effort. I understand the point of wanting the approval but maybe you should not show the new information until the approval is complete?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 19, 2012 at 1:02 pm
🙂
It is hard to explain and the process itself is very complicated.
More I work on it gets more confusing. :angry:
We want to show to our contacts what we have about them (contact info, assistant's info - which can be multiple, their medical subject of interest which can be multiple etc). and ask them to update, delete or insert anything new.
Our web site became a web application, even though we didn't mean to go that far 🙂
And we want our contacts to see info they update, insert or delete.
I thought that updt tables would keep insert or update and del tables would keep delete info.
I came up with this query (with help of your suggestion) for selecting data and displaying in the web fields.
Select c.P_Number,
FirstName =
CASE
WHEN delc.FirstName Is null Then ISNULL(updtc.FirstName, c.FirstName)
ELSE Null
END,
MiddleName =
CASE
WHEN delc.MiddleName Is null Then ISNULL(updtc.MiddleName, c.MiddleName)
ELSE Null
END
FROM Contact as c
LEFT JOIN del_Contact as delc ON c.P_Number = delc.P_Number
LEFT JOIN updt_Contact as updtc ON c.P_Number = updtc.P_Number
WHERE c.P_Number = @userid
July 19, 2012 at 1:16 pm
dtopicdragovic (7/19/2012)
🙂It is hard to explain and the process itself is very complicated.
More I work on it gets more confusing. :angry:
We want to show to our contacts what we have about them (contact info, assistant's info - which can be multiple, their medical subject of interest which can be multiple etc). and ask them to update, delete or insert anything new.
Our web site became a web application, even though we didn't mean to go that far 🙂
And we want our contacts to see info they update, insert or delete.
I thought that updt tables would keep insert or update and del tables would keep delete info.
I came up with this query (with help of your suggestion) for selecting data and displaying in the web fields.
Select c.P_Number,
FirstName =
CASE
WHEN delc.FirstName Is null Then ISNULL(updtc.FirstName, c.FirstName)
ELSE Null
END,
MiddleName =
CASE
WHEN delc.MiddleName Is null Then ISNULL(updtc.MiddleName, c.MiddleName)
ELSE Null
END
FROM Contact as c
LEFT JOIN del_Contact as delc ON c.P_Number = delc.P_Number
LEFT JOIN updt_Contact as updtc ON c.P_Number = updtc.P_Number
WHERE c.P_Number = @userid
That isn't too bad. Still might be a bit cleaner if you used a single table for both update and delete. Then just add a second condition to your where.
FROM Contact as c
LEFT JOIN ContactNew as delc ON c.P_Number = delc.P_Number and delc.OperationType = 'D'
LEFT JOIN ContactNew as updtc ON c.P_Number = updtc.P_Number and updtc.OperationType = 'U'
Otherwise you have three tables to hold what is effectively one row. This would at least keep it to two. 😉 Either way is probably OK.
If you do want to go with the three table schema I would offer a suggestion about naming.
Instead of:
Contact
del_Contact
updt_Contac
You might consider:
Contact
Contact_del
Contact_updt
This does two things. It will sort all three tables together in SSMS and it make it very clear that those three tables are closely related.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 19, 2012 at 1:22 pm
Actually I like this idea of 1 table with flags 'U', 'D' etc.
Thanks again for all these suggestions and ideas. I learn a lot today.
DD
July 19, 2012 at 1:31 pm
dtopicdragovic (7/19/2012)
Actually I like this idea of 1 table with flags 'U', 'D' etc.Thanks again for all these suggestions and ideas. I learn a lot today.
DD
You are welcome. Hope your project goes well. Post back if you have any other issues and I will do what I can to help.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply