March 28, 2019 at 10:49 am
Comments posted to this topic are about the item Understanding Your Database
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 10, 2019 at 9:41 am
One that always gets me is when to rename a column / table / other element that is named antithetically to it's purpose, or even just misspelt. That nags at me nearly as much as an unused table or procedure.
"Spending even an hour to decide if I should remove a 10 row table whose purpose is unclear doesn’t seem like a good use of time."
That's a decent evaluation certainly. But when you've got a column named ProcessManufacturerLevels or something and it's causing invoices to generate - you know there's a bunch of code or procs that will be affected but equally everyone that comes after is going to be looking at the flags wondering which one does what.
Generally I guess it's good to correct before deployment has occurred but once that's happened - well, maybe sleeping dogs shouldn't be poked...unless they really should 🙂
April 10, 2019 at 12:52 pm
We went through a formal process to do this to ensure that we knew exactly what GDPR exposure we had. It took ages because we had undocumented processes that either know one knew about or didn't realise that a call to ALL STAFF to identify such processes required them to consider themselves a member of ALL STAFF.
Things age out of cache. Good luck with monthly/quarterly mystery processes.
Unless you have access to the all the apps, code and reports that call your DB I would avoid attempts to cull tables in splendid isolation. The exceptions would be as follows.
The mantra is agree and enforce, particularly with sandpit schemas.
April 10, 2019 at 2:39 pm
This editorial is very apropos for me. I'm involved in a new project to take an old, legacy application written years ago as a Microsoft Access app, into modern technology. Of course, since someone unknown in the past threw the Access app together, there is no documentation. To compound the difficulties, for reasons unknown, 2 business analysts are working on redoing the database. And they don't give us the whole thing, or even allow me (the developer) or the DBA to review the schema that they're developing. Instead, they'll dole out a new table definition, about once a week, and tell us to pull data from one or more of the old database tables, to put into the new table. It really frustrates the DBA, as he's recently finished an expensive course (for which he paid for) on database design. So, now he's just relegated to writing the SQL script to get the data out of a table/some tables and put it into the newly released table. When they're finished it will be like we're working with a new legacy system. So, yeah, this editorial is just want I'll need. I'll be watching what insights I can learn on how to understand an "old" database.
Kindest Regards, Rod Connect with me on LinkedIn.
April 10, 2019 at 4:37 pm
Rather than dropping the table, I'd reccomend denying select permission, and then wait at least 45 days before dropping it.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
April 10, 2019 at 6:57 pm
Rather than dropping the table, I'd reccomend denying select permission, and then wait at least 45 days before dropping it.
....and then finding out it was used for 1099s next year.
My question is, does Michael sit in the server room and GREET the databases properly?
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
April 10, 2019 at 6:57 pm
“Spending even an hour to decide if I should remove a 10 row table whose purpose is unclear doesn’t seem like a good use of time.”
How does that compare with a few developers spending 10-15 minutes every few months trying to figure out what the table is for and whether they should keep it updated? (And another half-hour griping to anyone who'll listen about it.)
My preferred approach (so far) has been to do a little research to see if it's used, and if nothing found rename it as <table name>_MaybeObsolete_<date>. If no one screams within a year or so then it gets deleted.
April 10, 2019 at 10:52 pm
....and then finding out it was used for 1099s next year. My question is, does Michael sit in the server room and GREET the databases properly?
I tend to follow the "somehow hide this for 375 days" mantra. To catch those once a year processes that might be slightly delayed next year.
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 10, 2019 at 10:53 pm
It's a really hard line to draw in where/when/how long to evaluate items in a database. I tend to defer most evaluations until I need to do them or have time. Then I pick them off the list over time.
Michael is doing this because there are direct costs he can reduce by making changes, so they're working through a list, hitting low hanging fruit, then change criteria and do it again.
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 22, 2019 at 9:52 am
Personally I'm working for a very long period on the same basic database, and sometimes I add a table holding trace data before deleting data in a live production table. The name of such a table typically has the form "temp<ddmm>_<purpose>" where <ddmm> is day and month of creation. After time I can delete such tables, if no problems have occurred in the meantime.
Production tables should always have enough natural protection by adding foreign key constraints.
If I'm not sure of the use of a table, I would eventually rename it first by adding a "zz" prefix. If issues are reported, it is easy to rename it back to normal.
Of course getting into cleaning operations can depend on other issues like performance or storage considerations.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply