database schema design tools / modular database design

Question

I'm developing applications which can be build partly from modules. For example I would be able to develop some online community which contains the modules 'forum', 'blog', 'gallery', etc.

Currently I have one large database ERM containing all the tables of all modules with foreign-key connections and I'm using dbwrench to build this ERM. However, I am not very happy with this approach. I would like to have an ERM designer, which can work in a module-oriented fashion. I would like to save the database tables in a separate schema file for each module, but keep foreign-key references between these different schemas.

However, I was not able to find any tools, which support this -- is this the wrong way to go or how to you design "modular ERM" / modular database schemes?

thanks!

Answer 1

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.

MediaWiki Design - See notes at the bottom

Answer 2

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.

Answer 3

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.

Answer 4

I prefer using schemas. It is a natural way to encapsulate an area of concern (be it a schema for a module or a schema to cover an information-area).

I am using PostgreSQL and I prefer writing the db initiation myself (I want to have 100% control and SQL is as explicit as it gets.). I use SchemaSpy to generate the ER diagrams. I have had no issues with multiple schemas and foreign keys across schemas - not sure how that works in MySQL.

I am not familiar with the tool you mentioned, however, a screenshot seems to reveal they support schemas. Might be worth to check again. http://www.dbwrench.com/screenshots/xp_explorer.shtml

As for modular design, I am not sure schemas will be sufficient, imho schemas makes it easier for the brain to make assumptions about how the data relates, it does not make anything more modular per se. Please clarify your needs on how they should be modular.