There's the user data. Shipping addresses, billing addresses, customer preferences and passwords.

There's the order data. This consists of credit card data (associated with an order's shipping address), shipping information, handling instructions, line items and associated prices, taxes, etc.

There's the catalog data. This includes the product, category and SKU information, as well as the templates used to display all of the above on the website, and the related media.

There's the promotion data. This covers stuff like "two for one deals", "15% off the items if you buy three" and the like. There are also store coupons which are available.

Finally, there's all the miscellaneous data (search data, company jobs, store locations and administration functions).

So there are many, many tables that go into the making of a website. Sorting out all these tables can be a real headache.

Something that Dan Brandt showed me was the idea of organizing tables by their schema. The first time I saw this, I didn't see the point. They're all tables. Most of them are related in some way. Why complicate things by putting different tables into different schema?

But Dan was right. Schema are the closest thing to namespaces in the database world. They allow different types of data to have database settings (rollback segments, tablespace size, etc) to be assigned, because they grow at different rates. They allow tables to be swapped out, because you can point from one schema to another. They allow for better security, because you can apply the same security roles to everything in a schema.

And finally, they allow for a clear separation of concerns. You don't have to filter out all the order tables from the profile schema because it's simply not there.

The downside is that you have to be more clever about how to arrange your DDL scripts. This took some fiddling around with Ant, but was well worth it.

We didn't want to hardcode our schema names (at one point in development they were changing every week) and so we used an ant copy task with a filter which took the ${order.schema.name} and replaced with the appropriate ant property value when deploying the application.

This change fits into a larger ant build process that was in wide use at ATG for a number of years. Create and delete scripts are stored in specific directories and are run in order as a list. We also took an snapshot of the repositories using startSQLRepository, and dumped all the data out to XML files that we could use later on the newly created tables. The end result of this work was that we could completely destroy, recreate and value the entire database with a single Ant command as part of a daily build.