11

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?

Community
  • 1
maru
  • 183
  • 7

4 Answers4

12

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

Mark Storey-Smith
  • 31,860
  • 9
  • 90
  • 125
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.

user824276
  • 211
  • 1
  • 6
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.

Bradley Weston
  • 133
  • 1
  • 6
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.

Adnan
  • 1