Blog 
SQL and Normalisation Posted @ 17:46 on 15th September, 2008
I was just having a discussion with a co-worker about some SQL they were doing. They had 3 tables, which looked something like this:
user( user_id, ... );
pages( page_id, ... );
comments( comment_id, user_id, page_id, ... );
So that people can comment on both users and pages. The user_id and page_id in the comments table both defaulted to NULL, and only one would be non-null at any one time. He argued that this was a fine solution to the problem since it minimised the number of tables you had, and that he would gain no actual data storage by separating it out and having two join tables between user-comments and page-comments. I disagree.
I believe that since a comment is an entity which can be applied to numerous things, the table for it should be self-contained, i.e. not have any foreign keys for the things it is applied to. This changes, of course, when the comments apply to only one thing, when it stops being "comments" and starts being "user_comments", for example. Although the actual relation between user-comments and page-comments is one-to-many, because there are multiple entities I believe it is different case, and thus deserves join tables as per a many-to-many relation. In terms of code, having the join tables makes things a lot simpler (although he argued that you lose a bit of efficieny since you need two queries to save an entity instead of just one for his design). Also, flexibility is greatley increased with join tables - what if the client wants to add a "media" table, which can also have comments? Are you going to have to go back and modify all your queries so that it caters for having 3 possible null fields instead of 2?
My collegue was asking me because he wanted to do a query which did some joins, and then did some other joins based on the value of the non-null field in comments. I wasn't sure of the actual query, so don't know if the join-tables solution would have helped (though it proably would...), but he was going down the route of adding SQL conditional statements into his query. Again, here I believe that as little logic processing as possible should be done in everything except the language you are building in (be it PHP or whatever).
What do you think? Am I over-normalising my tables?
Cuppuccino Part 2 Posted @ 11:39 on 8th September, 2008
I spent some of this weekend playing with Cappuccino, the new Javascript Web App framework from 280North. And I must say, I really like it.
It's basically an attempt at a web port of the Cocoa framework, and to bridge the gap a bit more between the two it has it's own language, Objective-J, which is similar to Objective-C, which is used for building Cocoa apps on Mac OSX. This means you can create gorgeous looking applications very easily, and you don't need to worry about DOM, HTML, CSS etc, it takes care of all of that for you.
The big attraction for me is to give myself a bit more familiarity with the Cocoa framework and Objective-C. I've wanted for a long time to be able to build apps for Mac OSX, but have never been able to figure it out. This stems from my mindset as a web developer - I believe the process of building for the web and for the desktop are very different. Cappuccino goes a good way to brdiging this gap, so hopefully the next time I launch up XCode to build something it will all make a bit more sense.
There are still a lot of things I need to figure out with Cappuccino, but hopefully the answers will become more apparent as mnore documentation and examples appear. I'm really excited to actually build a full app using it. Just need to find the time now!
Cuppuccino Posted @ 15:02 on 5th September, 2008
I've been excited about this for a while, and now it's here! I'm still very excited - can't wait to get my hands in there and build something with it.
Lessons Learnt, Part 1 Posted @ 14:28 on 3rd September, 2008
As I mentioned yesterday, we killed our startup. I've been thinking a lot about the things I've learnt from the experience, and after yesterday's post about starting work immediately, something else has come to mind.
I'll be the first to admint that I'm not a great graphic designer - though I do enjoy doing it. Similarly, my interface design isn't great. However, I enjoy doing it. So I'd taken on that role in MeejaCake. One of the big things about MeejaCake was that we were studying the relationship between objects, and so we needed a good interface to allow people to add those relationships. For some reason, I went for the standard, Web 1.0 way of doing things, which was to have separate forms for each relationship and force the user to navigate between them. I knew this wasn't the best way, but for some reason, I stuck with it. It didn't work very well. I thought a lot about how I could improve the forms to make them work, but what I didn't consider was a completely different way of doing the forms, which is what I should have done.
When I hit a brick wall in programming, often I'll try and code the same problem a different way, to see if that produces the same errors or results. Most times, I'll find I come up with a better solution than my first attempt. If I had approached the interface design like a programming problem, I would have come up with a much better interface, but for some reason i stuck with the one that didn't work and kept trying new things to make it work.
My A-level Computing teacher always told us "There's more than one way to skin a cat". I should have applied the same rule to interface design. I know I will in future!
For those interested, what I believe I should have done is what the Amazon product does - which is instead of presenting you with a form for each relation, it shows you the entire page (i.e. the page with all the relations on), and allows you to simply add new ones, one at a time. This makes the back and front end code much neater, and since you can do everything with AJAX means the form is a lot more intuitive. Also, users don't need to learn two layouts, one for the page and one for the form - once they see the page, they will immediately know how to edit it.
Lesson: learnt.
Death of a Start Up Posted @ 12:53 on 2nd September, 2008
Advice: if you get a good idea, DROP EVERYTHING. Do it. Do it now. Don't sex, sleep, eat, drink, dream until it's done. Why? Because the moment you think of the idea the window of opportunity starts to close. Unfortunately for us, the window closed just as we were thinking about leaving private beta. Someone else had the same idea - and that person was Amazon. No way in hell can we compete with them, so we've closed the startup. Which, though disappointing, means we were definitely onto something. We wish, however, that we had just done it 18 months ago when we had the idea.
Ah well, back to the drawing board.