I have been trying to nail down a design for a sizable software project. I know how I want the system to behave. I just hadn’t nailed down a good API or internal structure for it yet.
I knew that I was going to have to track multiple versions of uniquely identifiable data with different versions stored in different locations.
I had all sorts of UML drawing on whiteboards and graph paper and in OmniGraffle. Everything just kept getting messy.
Earlier this week, I read a great article about Using SQLite on the iPhone. So, today, I tried to hammer out what I would do with this data if I were using SQL to track it. I wrote a bunch of CREATE TABLE statements and then a few SELECT statements.
It worked. Getting the JOINs in the SELECT statements to make sense forced me to make the relations in my data as simple as possible, but no simpler.
I had been trying to jam many-to-many relationships into one-to-many or many-to-one relationships. The SQL exercise forced me to get it right.
Will I use SQL in the final project? Maybe, but it doesn’t seem like I will need it. Did I need to use SQL for the design? Absolutely.