I've taken on some side work for this non-profit and last week was my first week on the job. They have two SQL Server Databases each with many tables in them that represent over 20 different applications such as

  • Course signup - Trainers can signup to teach different available classes
  • Course Approval form - trainers complete this form to get new courses approved to be added to the list of available classes.
  • Schedule - schedule of important dates for a term (holidays, start date for classes, etc.)
  • Trainer - list of all trainers and other information about a trainer (contact info, education, etc.)
  • Training Calendar - List that students can look at to see what's available for the current term
  • Training plan - a report that is printed for student with all of the classes they've signed up for
  • Certificate - Print a certificate for a student once the training course has been completed

There are about 15 other applications but it's all related to trainers, training, courses, students, etc.

This data is all over the place as there are no relationships between tables and very tedious processes have to be done to complete simple task. For example, for every new term you have to copy the data in the "old" table, create a new table, paste the old data into the new table and append the new data for the current term to the new table. So there's a table for every term (3 terms per year) for the past 10 years and that's only the beginning.

There's a lot of copying and pasting from Excel to SQL Server to get the data into the database and their front-end to manipulate the data isMS Access which I'd like to replace with ASP.NET (they have IIS 7).

To properly architect this and have good documentation, I've installed Visio 2013 and I'm learning about UML. So that boss can know exactly how this system will be re-architected, and how all the pieces will fit together what UML diagram should I create for him? Would that be a component diagram? I need to give him the 10,000 foot view so something like use-case, sequence, or database diagrams is way to low-level for him.

  • 2
    Architect your database schema. Then find the biggest point of pain and fix the pain. Success breeds confidence. Fixing the biggest pain points will make everyone happy and you won't have to produce any reports for anyone outside of your development group. May 25, 2013 at 19:55
  • The 10,000 foot view could be "Jane currently spends 6 hours a week managing data, with these changes, she will be able to do it in 1 hour per week, with much less risk from user error" or a list of process that are currently manual and will be automated.
    – JeffUK
    Mar 28, 2018 at 11:12

2 Answers 2


don't, unless he actually asked for it.

Unless the manager wants to know what's going on beyond "your databases were written inefficiently", and the text in your question. DO document the old and new designs, but do it for the programmer doing your job.

If management wants specifics, Visio its a good tool. But an abstract diagram listing "apps" and "data" should suffice. A separate diagram for how the "data" is stored, with consistent names to the first diagram, should suffice.

  • +1 for don't do what you're asking, but maybe a swim-lane based process map (i.e. BPM or a Flowchart) for the processes you are going to simplify as a result of your change?
    – JeffUK
    Mar 28, 2018 at 11:10

I would opt for using a Package Dependency/Domain Model diagram to relate all the various applications([super] classes) you have found. Obviously you will do this on a very very high level of abstraction first, then gradually dive deeper. This should effectively help you develop your ASP.NET application when it comes down to how to access, edit and store the data for retrieval into the front end system.

When and while you have a growing understanding of the business-data flow, you could also develop an Activity Diagram or a Business Process Diagram. The Activity Diagram should be easiest in Visio however if you are familiar with the BPM Notation, grab a stencil if you don't already have one; search "Visio 2003 BPMN" in your favourite search engine and you should get something like this.

These two diagrams are very useful for your eventual redesign work (requirements gathering), so doing them now, and showing them to those who know and understand the business will help you get refinements and an accurate representation of what the system is doing versus what it is expected to do. Review, review, review your work with those who are working there as much as you can so they all gradually 'learn' the benefits of what you are doing and the approach you are taking.

This is a sample from my modelling library that could serve as a guide to the structure you need to capture and the level.

The purpose of the object diagram is to test your domain model artefacts with the information that is flowing within the application/business so if you are missing anything then you have not correctly represented the business elements.

Here is a tip: whenever I draw diagrams which may look complex, I put them in PowerPoint and disguise them with small bullet points which help the business to understand where I am going with this.

Continue building the Glossary (in Excel) of every terminology or definition they have of their processes - just as you have done above. It works very well in a presentation when you have the terms spilled out clearly to back your designs.

Remember your UML Model is a collection of diagrams and descriptions so you are definitely on the right path. I am sure these can inspire the data model for communication and review when you move to the next phase.

Finally your component diagram can show the various parts as is and then make another one to show how you intend to simplify it.

  • Why build a "glossary" in Excel, as opposed to Word or Access?
    – DougM
    May 26, 2013 at 16:44
  • My first response based on experience is, it is easier to transform into a database table or HTML table when you finalise it and have a need to publish it to everyone. The next advantage is when you have references among different applications, you can make very good use of the sheets as opposed to multiple word documents. Access on the other hand is as good as Excel, however you have the overhead of how you get reviewers to quickly input if they don't use Access.
    – LAK
    May 26, 2013 at 17:17

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.