Tips on managing a large number of database tables for a given database model

Question

I am working on a MySQL database with over 60 tables. I use MySQL workbench for database modelling. I have broken down the model into several diagrams.

However I find it very difficult to manage this large number of tables.

Can anyone provide advice as to how to manage a large number of tables when working on a database model?

For instance, what is the maximum number of tables an individual diagram should contain?

Are there guidelines on how to break down the model into different diagrams? I guess each diagram should correspond to a module in the application..

Apart from breaking down the model into several diagrams, are there other ways of managing the complexity of a model?

Answer 1

60 is not really all that many.

I would definitely make one overall diagram for the model.

If you find that there are disjoint parts of the model that do not link to the rest, then those can also get their own diagram

then a series of small - topic related diagrams may be useful for end user documentation

Answer 2

60 is not a particularly large number of tables, but I understand your issues.

A diagram should contain as many tables as it needs to be well understood. No more, no less. However, what you should consider is how to organize those tables into more functional units to make your diagrams easier to understand.

For example, let's say that you're doing an invoicing system, with customers, orders, line items and payments. This ties to your inventory system with SKUs, vendors, current inventory, open shipments, and so on. When you are modelling your inventory section, even though you might need fields from customers or line items, drop that into your diagram as single object. It makes the diagram easier to model.

Views would actually be the physical representation of these conglomerate objects, if you always needed the same information in the same structure. For example, maybe a line item always needs to get the order status or the customer name. Create a view, and then use that to represent the 3 tables in your diagram.

The map is not the territory. Think about what you are trying to represent with each diagram and how to easily represent it. A diagram doesn't necessarily need to have each and every table that it's going to use on it, if you can point to another diagram that would explain it.