I have an application tracking social services delivery that has about a dozen modules for a wide variety of purposes, but they all focus on a single entity, the “client.” Consequently, the data design is a single DBC with many tables that have a persistent relationship to a “client_id” field that is used for RI, primarily cascading deletes.

The problem is it has grown into a monster. There are over 250 tables, and managing the whole thing in VFP’s Database Designer is a bear simply because of the physical on-screen size, number of tables, and a massive spaghetti bowl of relationship lines. Changing RI code takes forever, and the whole thing makes me very nervous. Now I need to add a new module, and I dread adding yet more tables to this DBC, but they still need to reference that base “clients” table.

What’s your opinion on the best way to handle this? Should I just forge ahead with the current database, and hope the whole thing holds together, go to another tool like xCase, break it out into separate databases and write a bunch of convoluted, home-brewed RI code to handle cascading deletes, or something else I haven’t even thought of? Any suggestions from someone who has “been there” would be appreciated.