Simply on Rails - Part 2: Database Design

Saturday, 14 July 2007

This week I attended a course for work on how to Implement Databases with Microsoft SQL Server 2005. An interesting course indeed, which made me realize how feature-rich Bill's product is, compared to the Open Source alternatives like MySQL. It also made me realize how nice it is to implement database-related logic (read: Models) using a proper programming language rather than using triggers, stored procedures, functions and other goodies offered by Transact-SQL.

It's all a matter of taste and of necessities: using MS SQL Server for one of my website is simply not going to happen anytime soon, and I'm more than happy to have a database which can be used just as a database and a programming language (Ruby, in this case) which can do wonders, rather than a procedural-only surrogate.

Anyhow, back to our weekly series. After creating a concept map, it's time of get real and try to figure out a database architecture. The tool of choice this week is obviously the widely popular DbDesigner 4. It's free, it's easy to use, and the results are pretty enough. There:

It's amazing how a relatively simple concept map can lead to such a complex database architecture, isn't it?
Well, it's normal. One of the reasons of this is that I totally forgot about geographical information about the houses which will be featured on the site, or better, I thought about it as a strings typed in by the administrators, whereas it would be much better having dropdown boxes.

Countries, regions, privinces, areas and cities will be added to the database only once, rather than having to type them in every time a house is added. Obvious, but this lead to five tables more and nine (!) relationships more.

The other reason of why the number of tables is higher than the number of entities in the domain model is that I decided not to use the ENUM type. Firstly because it's not handled very well by Rails
and also because there's a number of reasons why ENUMs should not be used.

The only problem now is that whenever I load a house, I'll have to get data from a lot of tables at once (and this means a lot of joins underneath the model layer) or – worse – a lot of queries in case I decide to load related data “on the fly”. It looks like I'll have to do a bit of piggy-backing here and there. Someone already thought about a way of doing this in a more “Model-friendly” way. Perhaps I'll give it a shot.