Tips for 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 to model the database. I broke the model into several diagrams.

However, it is very difficult for me to manage this large number of tables.

Can someone give recommendations on how to manage a large number of tables when working on a database model?

For example, what is the maximum number of tables a single chart should contain?

Are there any recommendations on how to split the model into different diagrams? I think every diagram should correspond to a module in the application.

Besides splitting the model into multiple diagrams, are there other ways to control the complexity of the model?

Answer 1

Besides dividing a database into groups of related tables, you cannot do this too much.

As the saying goes, there are some things you can do to make things easier to manage, like color coding your tables by type, like

• Associative tables (which are indeed Many-Many relationships)
• Search tables (which simply contain information about the value and are not the database core)

Answer 2

60 is actually not so much.

I would definitely make one general diagram for the model.

If you find that there are disjoint parts of the model that are not related to the rest, then they can also get their own diagram.

then a series of diagrams related to small topics can be useful for end-user documentation.

Answer 3

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

A chart should contain as many tables as it should be well understood. No more no less. However, you should consider how to organize these tables into more functional blocks to make it easier to understand your diagrams.

For example, let's say that you are making a billing system with customers, orders, line items, and payments. This is related to your inventory system with SKUs, suppliers, current stocks, open deliveries, etc. When you model your inventory section, even if you might need fields from customers or line items, put this in your chart as a separate object. This simplifies chart modeling.

Representations would actually be a physical representation of these conglomerate objects if you always needed the same information in the same structure. For example, it is possible for a line item to always require an order status or customer name. Create a view, and then use it to represent the three tables in the diagram.

The map is not a territory. Think about what you are trying to present with each chart and how easy it is to imagine. A chart does not have to contain every table that it will use on it, if you can point to another chart that will explain this.

Answer 4

Divide and win.

Logical namespaces reduce mental stress on everyone who deals directly with the database. Standard SQL has a way to handle this: SQL schemas.

See how other platforms support SQL schemas. ( How PostgreSQL supports SQL schemas .) MySQL has some kind of SQL schema support. (That is, no, really.) In MySQL, CREATE SCHEMA creates a database. This is not what you want here. (MySQL does not just provide foreign key constraints in databases.)

Since the SQL schema is essentially a namespace, you can simulate the use of SQL schemas by prefixing the table names with the "schema name". Thus, if you are building an accounting system, you can group all accounts receivable tables in the AR-scheme, all payroll tables in the AP-scheme, etc. You can do this by specifying the table names on "AR" or "AR_", "AP" or "AP_" and so on.

This brings SQL schemas closer in two ways. He organizes the tables first logically, then in alphabetical order. And it allows the same table name in several "schemas". (Like "AR_employees" and "AP_employees".)

I heard that they talked about foreign key support for federated tables in MySQL 6.something. But if it looks like traditional MySQL support for foreign keys, it will not help you.