Tuesday, October 23, 2007

Database "Design By Use"

About two years ago I designed a database for managing customer information, billing information, product information, pricing information, shipping information, and subscription information. The method I used to design the database, tables, and queries was Design By Use (DBU).

I can not share the resulting system but I can tell you how DBU helped with the design process.

The first step is to list as many of the "requests" you can define at the time. For example:

Find all of the customers who purchased product X during the past 6 months.
Find all of the products for sale in Spain.
Find all of the monthly subscriptions that have expired.

These examples are how the system will be used, thus we are designing the system in the manner we want to use the system.

The above examples eventually became SQL queries.

At the same time the usage examples are helping formulate the design the domain objects are being identified as well. The domain objects eventually found their representation in the database tables either as a table schema or as the results of joins on various tables.

Knowing how the database was to be used also helped in figuring out the schema and which tables might need keys to be used in other tables.

In the end I had designed a system that was exactly what was needed, thus YAGNI was satisfied. Also it was demonstrable in that the queries could be ran against test data and through automated testing show that the system correctly works.

I have designed many databases over the years but this approach was by far the best. It was better because it did exactly what was needed, no more, no less, and that I could run the queries as regression tests.

Often I have dealt with cross database joins in other systems and often I have found that such activities are the results of poor design.

Design By Use works great for me. You should try it and see if you find it helpful as well!

No comments: