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

I am wo开发者_高级运维rking 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?

---

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

---

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.

---

Divide and conquer.

Logical namespaces reduce the mental load on everyone that has to deal directly with the database. Standard SQL has a way to deal with that: SQL schemas.

Take a look at how other platforms support SQL schemas. (How PostgreSQL supports SQL schemas.) MySQL has peculiar support for SQL schemas. (That is, none, really.) In MySQL, CREATE SCHEMA creates a database. That's not what you want here. (MySQL doesn't easily enforce foreign key constraints across databases.)

Since a SQL schema is essentially a namespace, you can mimic some use of SQL schemas by prefixing table names with a "schema name". So, if you were building an accounting system, you could group all the accounts receivable tables in an AR "schema", all the accounts payable tables in an AP "schema", and so on. You might do that by prefixing the names of tables with "AR" or "AR_", "AP" or "AP_", and so on.

This approximates SQL schemas in two ways. It organizes tables first logically, then alphabetically. And it allows the same "table" name in multiple "schemas". (As "AR_employees" and "AP_employees".)

I've heard there's talk of support for foreign keys to federated tables in MySQL 6.something. But if it's like MySQL's traditional support for foreign keys, it won't help you.

---

Other than partitioning the database into groups of related tables, there is not too much you can do.

That being said, there are some things you can do to help manage things a bit more easily, such as color-coding your tables by "type", such as

1. Associative tables (Ones that are really just a Many-Many relationship)
2. Lookup tables (Ones that just hold value information and are not the core of the DB)