Patterns for database functional decomposition?

Question

we have a following problem. Our enterprise application has a database in MySQL, and it seems like it's structure becomes too complex - where are more than 100 tables (it has been developing for 7 years now).

However, most tables don't have any relations to any other tables at all. There are some common tables (dictionaries) that are used by everyone (about twenty such tables), but that's all. The question arose how to make this database faster and more reliable.

I read a lot about database decomposition. That is, you place table relating to different domains in different databases. For example, anything related to waybills and other paper stuff is placed in database called Papers, and anything related to the customers and their orders is placed in database called Clients and so on.

Here we have two problems:

• The user should see the enterprise application as acting on a single domain. The developers must change the core working with database in a way, so that it could address several databases.
• The common tables rise a problem. They have to be kept in a single database and cannot be replicated in all databases after decomposition. Therefore, how do we make a query SELECT * FROM A JOIN B, there A and B are in different databases (on different servers)?

Both questions actually address the same problem - are there any common Enterprise patterns (like GoF) for solving this problem? I am particularly interested for patterns suitable for Java EE.

Answer 1

I wouldn't necessarily start off by moving groups of tables into their own databases - this will make the system even more complex, and now you have additional issues like synchronising the backup of multiple databases. IMO 100 tables isn't unwieldy at all.

If however, there is a Business Driven need to split up the functionality of your system (and database) into multiple systems, then by all means do so. Obviously this would require major changes or even rewrites of the systems at the same time.

Re : Waybill, Invoice, etc unfortunately, MySQL doesn't implement Schema as other RDMS's like MS SQL Server. You could simulate the namespacing inherent to a schema by adding a prefix to tables (PaperWaybill, PaperInvoice, etc).

However, changing table names now is probably ill-advised given that your system has been in production for 7 years, and nailing down all the dependencies could be tricky - e.g. there may be several applications, reports, jobs etc all accessing these tables.

IMO you need to get down to the core reason why there are so few relationships. It is possible that the relationships exist, however, FOREIGN KEYS were dropped e.g. for performance reasons. If this is hindering your ability to diagram the system, or preventing a tool such as an ORM code generator from creating relationships, you could restore your PROD database to a DEV environment and add foreign keys back into DEV. At the same time, you would get an idea for the quality of data - e.g. FK constraint.