Understanding Your Database

  • Steve Jones – SSC Editor

    SSC Guru

    Points: 713653

    Comments posted to this topic are about the item Understanding Your Database

  • call.copse

    SSCoach

    Points: 16588

    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 🙂

  • David.Poole

    SSC Guru

    Points: 75019

    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.

    • Items in schemas used by DBAs only
    • Items in schemas where there is an agreed retention period (sandpit schemas)
    • Items with an agreed naming convention indicating archive/delete/backup artefacts

    The mantra is agree and enforce, particularly with sandpit schemas.

    LinkedIn Profile
    www.simple-talk.com[/url]

  • Rod at work

    SSC-Dedicated

    Points: 33042

    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,RodConnect with me on LinkedIn.

  • Eric M Russell

    SSC Guru

    Points: 124904

    Rather than dropping the table, I’d reccomend denying select permission, and then wait at least 45 days before dropping it.


    "The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."

  • jonathan.crawford

    SSCertifiable

    Points: 6249

    Eric M Russell wrote:

    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

  • Len.Geoghegan

    SSC Veteran

    Points: 200

    “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.

  • Steve Jones – SSC Editor

    SSC Guru

    Points: 713653

    jonathan.crawford wrote:

    ....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.

  • Steve Jones – SSC Editor

    SSC Guru

    Points: 713653

    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.

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply