April 7, 2010 at 6:58 am
These kind of issues arise all the time when migrating from a legacy system to SQL Server. As part of my current project I have written an SSIS package that transfers data from FoxPro free tables to SQL Server and FoxPro does not handle dates the same as SQL Server, even when you use a FoxPro date data type. When ever I encounter a data problem it goes back to the users of the FoxPro system to fix, even when I know what the correct data is. I do this for 2 reasons:
1. It is their data, not mine
2. I want them to understand that they need to be careful when entering data, because it changes the way decisions are made.
One of my coworkers thinks I should make the changes myself instead of "adding work" for the users. I think mistakes should be made painful for users so that they are more careful next time. Yes, the application should verify things better, but it doesn't and I have no control over that.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 7, 2010 at 7:04 am
Paul White NZ (4/7/2010)
David Fulton-420388 (4/7/2010)
I'm in the camp that the DBA should maintain the data entered by the users. If data looks incorrect, then bring it to the users' attention and let them research it and make the appropriate changes.Is there a 'not' missing from that first paragraph...? :unsure:
No, there isn't. My position is that the DBA should make sure the data is secured, backed up, etc., but that they should not be actively validating and changing data. If they see data that appears to be inconsistent, then they should report it to the users and let them check it out and fix it.
April 7, 2010 at 7:05 am
Of course we all know that this is why dates should never be stored as anything other than date types. This is as much a database design problem as a bad data problem. Fixing data errors is one of those "it depends" kinda things. If the record in question is part of a larger identifiable set (Say for instance a shiftid) then the other records can tell you if the date should be 2/28/2007 or 2/29/2008.
April 7, 2010 at 7:05 am
george sibbald (4/7/2010)
David Fulton-420388 (4/7/2010)
I'm in the camp that the DBA should maintain the data entered by the users. If data looks incorrect, then bring it to the users' attention and let them research it and make the appropriate changes.How?
sounds cart before horse to me. Should not proper constraints be set up in the database and or app to prevent bad data getting in there in the first place? a DBA cannot be held responsible for the data users try to insert, he can hardly sit there and watch data being entered.
Damn, thats no fun. dusted off my high horse and everything..............:-)
sorry David.
---------------------------------------------------------------------
April 7, 2010 at 7:06 am
David Fulton-420388 (4/7/2010)
Paul White NZ (4/7/2010)
David Fulton-420388 (4/7/2010)
I'm in the camp that the DBA should maintain the data entered by the users. If data looks incorrect, then bring it to the users' attention and let them research it and make the appropriate changes.Is there a 'not' missing from that first paragraph...? :unsure:
No, there isn't. My position is that the DBA should make sure the data is secured, backed up, etc., but that they should not be actively validating and changing data. If they see data that appears to be inconsistent, then they should report it to the users and let them check it out and fix it.
Well now I look silly. Sorry, George and David.
Rollback Transaction 🙂
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 7, 2010 at 7:13 am
Paul White NZ (4/7/2010)
David Fulton-420388 (4/7/2010)
Paul White NZ (4/7/2010)
David Fulton-420388 (4/7/2010)
I'm in the camp that the DBA should maintain the data entered by the users. If data looks incorrect, then bring it to the users' attention and let them research it and make the appropriate changes.Is there a 'not' missing from that first paragraph...? :unsure:
No, there isn't. My position is that the DBA should make sure the data is secured, backed up, etc., but that they should not be actively validating and changing data. If they see data that appears to be inconsistent, then they should report it to the users and let them check it out and fix it.
Well now I look silly. Sorry, George and David.
Rollback Transaction 🙂
I'm just confused. What does 'I'm in the camp that the DBA should maintain the data entered by the users' mean?:unsure:
---------------------------------------------------------------------
April 7, 2010 at 7:13 am
At my current assignment, we require a service request be registered to do just about everything which sometimes includes changing data.
Personally, I backup the data I'm about to change and the sql script I used. Which is always in the format:
/*
Begin Tran
Update something
RollBack
Commit
*/
Wrapping the transaction inside of comments helps to eliminate an oops moment as does putting rollback before commit.........
As far as data types, beware of out-of-the-box software solutions. One quickly realizes that you have no control of data types that the vendor chooses. :hehe:
April 7, 2010 at 7:30 am
Paul White NZ (4/7/2010)
David Fulton-420388 (4/7/2010)
Paul White NZ (4/7/2010)
David Fulton-420388 (4/7/2010)
I'm in the camp that the DBA should maintain the data entered by the users. If data looks incorrect, then bring it to the users' attention and let them research it and make the appropriate changes.Is there a 'not' missing from that first paragraph...? :unsure:
No, there isn't. My position is that the DBA should make sure the data is secured, backed up, etc., but that they should not be actively validating and changing data. If they see data that appears to be inconsistent, then they should report it to the users and let them check it out and fix it.
Well now I look silly. Sorry, George and David.
Rollback Transaction 🙂
No, I understand why both of you responded like you did. I used "maintain" with a different meaning than you both read it.
The DBA is responsible for database, i.e., making sure it is backed up, secured and accessible to the users. The users are responsible for the data in the database. The key is for the two to work together.
I am managing a conversion of legacy data into one of our systems and happened to notice that some records have a particular date set in the year 2217. I know the date is invalid (I was a user), but the data belongs to the client. We are responsible for the accuracy of our conversion, not the accuracy of their data. I told our data conversion team to import the date as is, and will report the issue to our client.
Best regards,
Dave Fulton
April 7, 2010 at 7:38 am
ben.mcintyre (4/7/2010)
While I agree with the sentiment of this article, I think the most important thing is: if you DO change any data, KEEP AN AUDIT TRAIL ! You MUST be able to undo ALL your changes later.
Excellent point. This is definitely something you need to do if you are making the decisions to change anything.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
April 7, 2010 at 7:40 am
I work in the healthcare industry currently (the grey in the beard = many different industries) and I've found that healthcare software is pathetic. We have a package from a monolith in the industry and it has [no] checks for valid data. Picture putting [any] character (or nothing at all) in a gender field, random characters in date fields, alpha in a SSN field, etc. The explanation: "tell the users not to enter it that way."
<rant deleted>
I believe the responsibility for data validation lies entirely with the developer and their design. Whether it is constraints on a column or checks at the data entry front-end, they're responsible for data validation. Once it hits the database, how in the heck can a DBA even dream of figuring out what was rattling around in the head of the person entering the data? We're just talking about data you can [spot] as wrong like '2/31/2009'. What about that extra zero in a medication dosage?
You can fix an application but you can't fix stupid. All you can do is prevent it.
April 7, 2010 at 7:41 am
When you change the data type, you can lose quite a bit. Dates have special functions and edits, and this is probably even more important when integrating data from multiple data sources.
Cleanup - users own the data, and need to be involved in any cleanup. If you jsut clean something up, a couple things can happen, which you might care about.
You got it right, and they don't realize maybe an edit is needed to keep things in order.
Or you get it wrong, and they don't trust the data. Everything is questioned, so data dumps and sifting takes place.
You may end up with multiple answers for the same question, depending on which expert you go to.
Audit trail - always good to be able to trace a record change, especially if you are reprocessing records for measurements.
Greg E
April 7, 2010 at 7:42 am
george sibbald (4/7/2010)
Paul White NZ (4/7/2010)
David Fulton-420388 (4/7/2010)
Paul White NZ (4/7/2010)
David Fulton-420388 (4/7/2010)
I'm in the camp that the DBA should maintain the data entered by the users. If data looks incorrect, then bring it to the users' attention and let them research it and make the appropriate changes.Is there a 'not' missing from that first paragraph...? :unsure:
No, there isn't. My position is that the DBA should make sure the data is secured, backed up, etc., but that they should not be actively validating and changing data. If they see data that appears to be inconsistent, then they should report it to the users and let them check it out and fix it.
Well now I look silly. Sorry, George and David.
Rollback Transaction 🙂
I'm just confused. What does 'I'm in the camp that the DBA should maintain the data entered by the users' mean?:unsure:
I read it as David entered it. The DBA "maintains" it, meaning what he said. They back it up, they ensure what is entered is secure, intact, and maintained as the user entered it. No changes are made by the DBA, or by unauthorized people. It makes sense to me, though I can see how you would interpret it differently.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
April 7, 2010 at 7:48 am
Steve Jones - Editor (4/7/2010)
george sibbald (4/7/2010)
Paul White NZ (4/7/2010)
David Fulton-420388 (4/7/2010)
Paul White NZ (4/7/2010)
David Fulton-420388 (4/7/2010)
I'm in the camp that the DBA should maintain the data entered by the users. If data looks incorrect, then bring it to the users' attention and let them research it and make the appropriate changes.Is there a 'not' missing from that first paragraph...? :unsure:
No, there isn't. My position is that the DBA should make sure the data is secured, backed up, etc., but that they should not be actively validating and changing data. If they see data that appears to be inconsistent, then they should report it to the users and let them check it out and fix it.
Well now I look silly. Sorry, George and David.
Rollback Transaction 🙂
I'm just confused. What does 'I'm in the camp that the DBA should maintain the data entered by the users' mean?:unsure:
I read it as David entered it. The DBA "maintains" it, meaning what he said. They back it up, they ensure what is entered is secure, intact, and maintained as the user entered it. No changes are made by the DBA, or by unauthorized people. It makes sense to me, though I can see how you would interpret it differently.
three countries separated by a common language!
---------------------------------------------------------------------
April 7, 2010 at 7:52 am
'Maintain' as in 'keep preserved in its original state', or 'Maintain' as in 'proactively fix something to bring it close to a chosen standard'.
Was going to comment earlier on the ambiguity, but didn't. Coulda woulda shoulda.
April 7, 2010 at 8:00 am
Paul White NZ (4/7/2010)
David Fulton-420388 (4/7/2010)
I'm in the camp that the DBA should maintain the data entered by the users. If data looks incorrect, then bring it to the users' attention and let them research it and make the appropriate changes.Is there a 'not' missing from that first paragraph...? :unsure:
Just how much "maintain" do you mean? That sounds dangerous. But I think I see your point that the application owner/service provider has to be a Trusted Advisor and be on the lookout for mistakes or discrepencies or things outside what is expected and then bring those to the attention of the customer so a decision can be made. The service provider grows in their knowledge by coming across new things and the customers get a value add to their service.
Viewing 15 posts - 16 through 30 (of 69 total)
You must be logged in to reply to this topic. Login to reply