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?
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