Understanding Your Database

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715375

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

  • call.copse

    SSCoach

    Points: 16839

    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: 75121

    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.

  • Rod at work

    SSC-Dedicated

    Points: 33125

    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.

  • Eric M Russell

    SSC Guru

    Points: 125010

    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

  • jonathan.crawford

    SSCertifiable

    Points: 6354

    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: 213

    “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: 715375

    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: 715375

    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.

  • martin.matten

    Newbie

    Points: 1

    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 10 (of 10 total)

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