0

I am forced to manually document a legacy enterprise database. Are there some standard good practices or tools to do this?

To give you some more context, I have recently been given restricted access to a legacy enterprise database. There is very little documentation. I don't have permissions to view system tables or generate ER diagrams. I am exploring/using the database and manually documenting it as I go.

I would like to represent the schema with code (manually) in such a way that it can be parsed, say, with Python, to generate an ER diagram?

Are there tools to do this kind of thing? Or standard good practices to document the schema, annotate definitions and common keys etc?

I'm imagining some kind of a tree or graph like data structure that can be used to encode the schema, parsed with Python etc, and used to generate ERDs, etc, outside of the db management tool; (since I don't have permissions to do this inside the db management tool (Oracle SQL Developer)).

zab
  • 101
  • 1
  • If you can generate the schema why not do so, then create the schema in a database you do have rights to and have it generate the ER diagrams for you? – Jonathan Fite May 29 '20 at 12:27
  • 2
    Why restricted access? If they want you to document it, surely they must give you access? I had to do a similar job (my first in IT!) and basically, it was like detective work - slowly, step by step! I don't have permissions to view system tables or generate ER diagrams. - this is madness - you've been hamstrung from the start. If your employers don't trust you, why not move on? What's the server? The one I worked on was a home grown system so there were no system tables or any real organisation - everything was ad-hoc - you are faced with a similar scenario, despite if being an RDBMS! – Vérace May 29 '20 at 12:40
  • Ask for more permissions. You wouldn't tell someone to fish without a pole or a net. Why would someone ask you to document a database without permissions to the schema? – Jacob H May 29 '20 at 13:33
  • Yup - good questions. Some more context:
    • The db is Oracle.
    • I was hired as a data scientist / ML engineer, not a db admin. The organization has a blanket policy to grant access based on roles. My role has very restricted db access (something about protecting customer privacy etc).
    • There does exist some scattered patchy documentation for some queries and automated jobs.
    • I'm after some systematic way of documenting it on the fly - (say in markdown, on git etc, that I can easily read and share with my team, and perhaps write some code to parse and draw ERDs in Python).
     – zab May 29 '20 at 18:14

2 Answers2

0

Since asking the question, I have found these two very nice web-based solutions: https://dbdiagram.io/home
https://alternativeto.net/software/quickdbd/
and a basic one:
https://dshifflet.com/erd/index.html

Unfortunately due to security concerns, I am not allowed to use 3rd party cloud solutions. It's gotta be a standalone desktop software.

I also discovered ERAlchemy and it looks like I can manually generate ERDiagrams in Typora using markdown.

Any other useful ideas?

zab
  • 101
  • 1
-1

... manually document a legacy enterprise database

If this database is for a paid-for, Packaged solution, first get hold of the Licence Agreement between between your company and the Package [software] supplier. It may well be that the "Reverse Engineering" of the database that you are being asked to perform is expressly forbidden by that licence. It usually is.

If so, down tools immediately.

Assuming you're [legally] allowed to carry out this work ...

... restricted access ... I don't have permissions to view system tables or generate ER diagrams.

Get the tools and, more importantly, the permissions you need to do this job.
Without being able to query system catalogs and so discern table structures and relationships, you are working blind.

Indeed, with the [very] little you've been given to work with, I think you should deliver the diagram below as your entire "Documentation". 

+---------------+
|               |
|    Package    |
| (proprietary) |
+---------------+
       |
       v
+---------------+
|               |
|   Database    |
| (proprietary) |
+---------------+

When they ask for more detail, ask them for more appropriate access to the database!

Phill W.
  • 8,706
  • 1
  • 11
  • 21