How to make E-R diagram with 500 tables?

Question

In our company we have a huge project with lots of tables. When I mean a lot it is about 500. Maybe it is something normal. Some of these tables have more than 200 fields. I find that trying to make an E-R Diagram for the whole system will be confusing, tiring and useless. Just including the tables would result in a frightening list of boxes. Not to mention the relations that will flow like spaguetti.

How do you document those projects? Is there any technique for divide and conquer the structure of a project?

By the way we use Microsoft Visio and our servers run Oracle.

Update: As everybody said we all agree that a 500-table ERD will be useless and needs to be broken up in pieces. The method of grouping tables in sub-diagrams is something much more interesting. How many tables are too much? What If I can't find clear routes for the relations? Is there any strategy or heuristic that may help?

Answer 1

Try to break your domain into multiple database diagrams, each containing tables that are closely related to each other.

Answer 2

You need to do two things:

1. Don't put fields on the ERD; and
2. Break up your model into logical groupings that are sufficiently small to diagram.

(2) is really the important one. You want to limit yourself to about 20-30 entities max (and preferably a lot less) per diagram. Most of the time noone will be concerned with the whole thing. They might be concerned with how parties are modelled, or accounts or some other part of the system and how those key entities relate to others.

It's OK (and expected) to repeat entities on different diagrams as appropriate.

There's really no other way to do it.

If it doesn't have to be an ERD and can be, say, a UML class diagram you have a bit more scope because you can high level entities that effectively encompass multiple tables so you can have diagrams at different levels. This depends on what's more important though: the ERD or the object model. Often you'll end up needing both.

Answer 3

The simple answer is: Don't. Even if your tool support diagrams of this size, the complexity of this will probably be of very little help to anyone viewing the diagram.

Hopefully the system is modularized somehow so you don't have to view all 500 tables at a time. If this is the case, use high level diagrams, that show the overall architecture. Each subsystem can then be described in more detail in additional diagrams.

Answer 4

You should try and break apart your database into as many smaller logical schemas, so that the pieces of the system which are most closely related (such as tables having to deal with users, roles, etc.) can be diagrammed together. If you can break apart your database into these logical subsets, then you will not only be able to build better diagrams, but you will also have a leg up in terms of managing the database itself.

Answer 5

Remember, diagrams are not created just to have them or because they look "cool". They should give the viewer an overview and valuable information. They are not made to confuse but to help explain the structure of whatever they are showing.

An ER diagram of 500 tables cannot be useful. In that case try to model just some (important/complex) parts of the system that you need to remember or explain to someone else.

Answer 6

As many others have rightly pointed out, trying to create a comprehensive and useful diagram of 500 entities and their relationships is going to be hard to do.

K. Scott Allen had a nice article on why trying to diagram large numbers of entities/objects using visual designers won't scale easily:

Visual Designers Don’t Scale (OdeToCode.com)

I'd suggest, as others have, breaking down your diagram into groups of related entities/functionality.

Answer 7

My application dbscript generates Data Diagrams displaying table names only. The functionality is intended to provide a quick overview of the data model, to be used as a basis for full data diagrams created in other applications.

See my blog for Oracle-based data diagrams and MSSQL-based data diagrams.

I never tried 500 tables with this tool, though.

Answer 8

No one will ever look at a 500 table E-R diagram except to classify it as abstract art. It's as useful as an English-Oxford dictionary printed on a single page in a 0.0001 point font. The purpose of an E-R diagram is to provide information to your implementers. There is such a thing as too much information. Break the system down into subsystens and document the subsystems separately.

Answer 9

Create Design Structure Matrix instead. It has table names as both rows and columns and puts an X where one table has foreign key to another. This way you can easily analyze huge structures that are impossible to put on a single diagram. Check out this paper (it's focused on DSM application to programming languages rather than to databases but the principles are the same).