database schema design tools / modular database design
I agree that modular design is the way to go. When we create applications for customers we tend to sell them a collection of widgets we have already built. So what happens when a customer says "I visited website X and I like their widget Y. Can you add this to my application/website?"
This is a good thing customer A pays for widget Z that we can then sell to all other customers. The trick is to build these widgets in such a way they so they fit without breaking the current application.
Check out this link and the sources identified in the notes.
I am absolutely convinced, this is the right approach. Unfortunately the database community has yet to embrace such new concepts as modular design, agile software development and the like.
If I have a choice I let a ORM tool create the base of the script, and add details that don't belong into an ORM (e.g. special indexes, tablespaces, partitioning) manually, and also create migration scripts manually (which is pretty straight forward when you have the text based diffs, between the complete script in two versions.
So I end up with three kind of scripts: automatically generated script that generates a new database.manually generated script that does the same, but with some added details, that are irrelevant for the functional requirements.a set of migration script that move a database from one version to the next step by step.
I also have a bunch of tests, that create various schemas, using combination of theses scripts and compares them.
If I need diagrams, I create those from the schema or the code of the object model, using some reverse engineering.
I keep separate database build scripts for each modules schema and just note in comments what other modules they are dependent on. I then add schema's to the database that corresponds to an application as necessary. Using normal Indexes instead of foreign keys. I've always found doing things manually is best for extremely modular tasks.