10

I recently got hired as the only IT Guy in a certain Company X and I am tasked to fix their applications, and in my opinion, the best way to start is by understanding the database.

Their current database is a MySQL database with 186 tables(note that some tables are empty for god knows why). And the application is communicating with the database through an MS Access database interface. (I ask myself why the developers did that too)

Question is, How do I start tackling this large undocumented database? Yes, it is undocumented because the application's developers aren't willing to give me an ERD or data dictionary or any information at all about the database to make my life easy. How would you suggest to take on this perilous endeavor of understanding every nook and cranny of the rather large database?

Related Question: How to dive into an ugly database?

Improve this question
3
  • 5
    Starting with the empty tables, slowly drop one table after another until the developpers are willing to cooperate...
    – René Nyffenegger
    Commented Jan 24, 2013 at 10:08
  • Think before you comment. The OP explicitly stated that he was the ONLY It guy. So who are those developers ? External contractors perhaps, who were paid for nothing more than just the time it took to build the thing ? Why would those be willing to put in their time for additional support, which company X probably has been unwilling to pay for to begin with ? Or were they perhaps former employees who got laid off because they were too expensive ? Why would they put in their time to help out company X with its problems ? And who is going to get hurt by dropping tables ?
    – Erwin Smout
    Commented Jan 24, 2013 at 13:04
  • 6
    @ErwinSmout I believe Rene's comment was intended to be interpreted as a light hearted jibe. That's how I saw it.
    – Mark Storey-Smith
    Commented Jan 24, 2013 at 13:22

4 Answers 4

Reset to default
11

The linked answer tackles the problem bottom-up, database first. As your responsibilities encompass the applications and database, I'd be inclined to attack this top-down starting at the application(s).

Focus your attention on understanding the most frequently used features of the application through consultation with the user base. Trace the database interactions of those features through profiling/logging tools so you can identify the key tables and procedures.

This way your early efforts are constrained to the "stuff that matters", rather than wasting time documenting tables and queries that may be rarely or never used. The focus should also bring the Pareto Principle to bare on your bug fixing efforts (so says Microsoft anyway).

Improve this answer
1
  • Thanks for the great answer. I honestly didn't think of that nor did I know that I can log the queries being run on the server. Thanks a lot!
    – maru
    Commented Jan 25, 2013 at 2:25
1

I would possibly try getting MySQL Workbench and then creating a EER model from the database. This means you can see what links to what and find out what the developers might of been thinking. All depends on the application also to how it is structured.

Improve this answer
3
  • I tried going that route, but it got quite annoying midway because there are 186 tables and workbench just slapped all the tables in the center of the canvas. and the small screen real estate wasn't helping things get smaller. But from the looks of it, there's no escaping doing things the hard way
    – maru
    Commented Jan 25, 2013 at 1:28
  • Don't bother attempting to do this with Workbench because it's riddled with defects (bugs)--you'll spend more time trying to overcome them than you will accomplishing anything useful. Instead, consider using Microsoft Visio to reverse engineer an existing database into a model as explained in this short article: link.
    – KXNV-89.1FM
    Commented Jun 23, 2017 at 19:32
  • A more apropos link: link -- "Reverse engineer an existing database into a database model" (with Microsoft Visio).
    – KXNV-89.1FM
    Commented Jun 23, 2017 at 19:41
1

I find DBLint to be useful for identification of problems with the database. It has following nice properties:

  1. It ranks the findings by their (subjective) importance, so you don't drown in a flood of minor warnings.
  2. The important findings are frequently indisputable and actionable.
  3. It scores the database with a single number, so you can measure your progress and compare the quality of different databases. I find it to be quite motivational.
  4. It is easy to configure DBLint to use only schema-based checks. So, it is fast to try it even on large databases. The only complaint I may have is that I have to run Windows to run DBLint.

For quick identification of hotspots on MySQL database, Neor Profile SQL is a handful proxy that sits between the application and the database. The beauty of it is that it is fast to setup.

For the discovery of the factual primary and foreign keys in the database, which are, nevertheless, not defined in the database, you may use Linkifier. For ERD plotting, the estimates can be exported into yEd, which has a plenty of layout algorithms for positioning of the tables. BPMN is my favourite for ERDs.

Improve this answer
0

There is a tool of oracle(My SQl workbench) to access My Sql database,it is a interface that could gave you the ERD of the database.

Improve this answer
1
  • yup, i'm familiar with workbench but it the reverse engineer function just dumps all the tables in the center of the canvas... 186 tables is annoying to sort out. know a way out of that?
    – maru
    Commented Feb 2, 2013 at 16:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.