My approach: 1. Model business processes that you expect to be generating data as well as those that will be asking for data. Both sides inform the design: ho

My approach: 1. Model business processes that you expect to be generating data as well as those that will be asking for data. Both sides inform the design: how hard it is to put data in, and how well the representation supports the different ways you want to get it out again. 2. Design the schema. Pen and paper is a good start, as are some tools that let you do it graphically. 3. Start writing down queries you expect to be able to do. Again, pen and paper/whiteboard. These force you to think through the relations and see if they make sense when you're building queries. It isn't uncommon for a schema that makes sense for putting data into a database to not quite be flexible enough to support the kinds of queries you care about. 4. Prototype, test. This entails not just coding up the schema, but connecting it to some source that will populate the database. I often write synthetic data generators to shove a bunch of data in so I can evaluate queries. This can inform schema revisions to address performance issues. SQLite is invaluable for prototyping. Last time I did this I used the SQLite manager plugin for Firefox to play with the database design as it was being designed in that 4th step above. Even if the plan is to move to a different database, SQLite is good for getting the rough picture right and playing with it with minimal work/friction. I have a couple database textbooks around that I refer to that are good references for SQL and schema design. I'd have to go dig them out of a box to find the names though - it's been a couple years since the last time I went through this exercise.