Documenting database

[TessB]

I haven't asked a question in years, but here it goes.

We have an Oracle financials database that is, let me tell you, so user unfriendly when it comes to table and field names, it is impossible to remember all the relationships.

Whenever I am asked for a report out of this database, I have to rely on views and comb through them to see where the data is stored and how to link the tables. It takes a long time.

What I would like to do is create some documentation that is easily searchable and explains the tables, purpose, and relationships to other tables.
Has anyone ever done something like this either to make their own lives easier and that of their colleagues? If so, how did you go about it? What format did you use? Do you have any examples?

Many thanks in advance. All suggestions welcome.

 

[Kryst51]

I have a program on my comp called Concept Draw Web Wave, it might be useful, I have seen one use of this software to show a database's design with links, table relationships, etc, plus you can add text boxes, etc. I haven't had time to delve into the program, I believe you can also link to a database and import your table setup into it. This was suggested to me to use for documentation as you can add various layers, etc.

 

[dcb]

Microsoft Visio will allow you to reverse eng over a ODBC

 

[Mr. B]

When I am asked to document something like this, I use the old reliable Word applicaiton. If you will start by documenting the name of your table table and immediately provide screen captures of the relationships and any other screen captures that will help you document the structure, then when you need info about one table you can simply do a search through the document for where it is documented. I also use Bookmarks and/or Hyperlink and Cross References to help with my documentation. The Bookmarks along with the Hyperlink will allow you to quickly move from one table descripton to a related table description. Also the Cross References allow you to quikly create and keep updated a list of the objects that you document as being in the database and the page numbers where they are located, like an index. You can even click on a Cross Referneced Page number and be taken directly to that page.

I recently finished documenting one database applicaiton that had over 100 queries that were executed during one routine. The document, including screen captures was just over 60 pages. When the documentation was delivered to the one who requested it, the only comment I got back was "that is really detailed."

Just my thoughts on the subject.

 

[TessB]

Thank you so much, everyone, for your input.
I do not have Visio, unfortunately, nor specialized software.
The Word document is very doable and I liked the idea of using Bookmarks and Hyperlinks.

The purpose of the document is to help people understand how the application uses tables to store data, so I think the Word approach will really suit my needs here.

Again, I appreciate your thoughts.

 

[RossWindows]

Sounds like you could even create a new database to document the oracle db

 

[gemma-the-husky]

but documenting a big system is not simple - if you have lots of dbs objects, then recording how it hangs together is very very difficult. You need to have people with a general concept of how the whole thing works, and as it gets bigger, this becomes harder and harder

what makes it difficult is you need someone with both a knowledge of IT, AND a knowledge of the business, and these are rare beasts.

just try building a html/hyperlinked help file for the app - and see how long it takes, and how hard it gets.

 

[RossWindows]

True. Essentially, you would have to mirror the way Access records relationships and then displays that data in a visual format in the relationsip window. Anyone who's ever looked at the relationships system table in access would probably agree with you Gemma. Myself included.

Difficult? Absolutely.
Possible? Anything is possible. How much time and money do you have?

 

[lcook1974]

I found this in the Sample Database section

Database Documentor:

http://www.access-programmers.co.uk/forums/showthread.php?t=173624

Not sure it would work or not since you are oracle.

 

[dportas]

Interesting that you found the Oracle Financials schema unfriendly. It has been a few years since I last worked with it but I always found it to be one of the most well-designed and easiest to understand of any financial / ERP system I know. Heaps better than SAP or PeopleSoft anyway.

Oracle used to provide documentation for the schema, including diagrams and a data dictionary in soft form (PDF as I recall). So check with an Oracle rep before you do a lot of work yourself.

 

[mascutax]

In your current form layout you are using checkboxes to denote the accreditation type if you chang ethis to an option group. Thus preventing more than one accreditation type being selected this should not mess up your form layout.

 

[georgedwilkinson]

I always used Oracle Designer for this. It draws physical and logical designs and allows you to build from inside Designer.