I'm new to a small company that's pretty much run by one guy. He knows all the ins and outs of the system which is quite large. We have a database with about 300 different tables. I'm struggling to keep track of where everything in the database is and what relates to what. What methods can be used to manage large databases, or does it mostly come down to going through other examples in the code base to find what you need?

  • Are you dealing with an ORM? – npinti Mar 23 '15 at 9:57
  • 4
    Documentation? Its no different to any other large system. You just have to get in there and understand it, bit by bit, until you're an expert too. – gbjbaanb Mar 23 '15 at 10:21
  • 2
    I misunderstood the question; for me a large database is a multi-terabyte or a petabyte one (it could have few tables with few columns, but it should have many rows) – Basile Starynkevitch Mar 23 '15 at 11:28
  • 1
    @Peck3277: How to eat an elephant? One bite at a time. Your question is very similar to this one (programmers.stackexchange.com/questions/155488/…) or this one (programmers.stackexchange.com/questions/6395/…), and I guess you can get the answers you are looking for from those question & answers. – Doc Brown Mar 23 '15 at 14:16
  • 1
    Just repeatedly ask the other guy who didn't bother to document anything. Maybe he'll get the hint. – JeffO Mar 23 '15 at 14:27

For the database that I am responsible for I do the following:

  • expose all metadata in views so you can see groups of related tables in a more human readable fashion
  • every view/table has a comment, every column has a comment. Yes, it's a pain but it helps new people
  • every function/procedure/trigger has a one line comment that starts with Summary. I extract the procedure/function name and comment into a table.
  • Toad has an option to document your database as an html file. It makes for dry reading but it is another means to access the structure
  • have a library of standard queries used to access commonly requested information
  • have packages that will implement commonly requested items like access, new metadata, disabling metadata
  • don't be too clever. That brilliant code you wrote in a flash of insight is not likely to be understood by you six months later or a new team member. If you must, document and comment the code.

What have worked for me is:

  • Plot a giant physical model of the database.

You must use a tool like Sybase Power Designer and reverse-engineer the model. There are also free tools that could be used. Once there you must find a plot service and have it plotted in big format. Then put it on the wall.

It helps if you first re-format the model for better readability of relatioships. What I do is:

  • Use non-diagonal lines
  • Have the join printed with every relationship
  • Prevent overlapping lines.
  • Minimize crossing lines
  • Group tables in clusters

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