contents/articles/simply-on-rails-2-database-design.html
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
----- title: Simply on Rails - Part 2: Database Design content-type: article timestamp: 1184405220 tags: rails|databases ----- <p>This week I attended a course for work on how to <em>Implement Databases with Microsoft <span class="caps">SQL</span> Server 2005</em>. 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 <em>proper</em> programming language rather than using triggers, stored procedures, functions and other goodies offered by Transact-<span class="caps">SQL</span>.</p> <p>It’s all a matter of taste and of necessities: using MS <span class="caps">SQL</span> 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 <em>just</em> as a database and a programming language (Ruby, in this case) which can do wonders, rather than a procedural-only surrogate.</p> <p>Anyhow, back to our weekly series. After creating a <a href="/blog/simply-on-rails-1-concepts-map">concept map</a>, it’s time of <em>get real</em> and try to figure out a database architecture. The tool of choice this week is obviously the widely popular <a href="http://fabforce.net/dbdesigner4/">DbDesigner 4</a>. It’s free, it’s easy to use, and the results are pretty enough. There:</p> <p><a href="/files/italysimply_database-architecture.png"><img src="/files/italysimply_database-architecture_thumb.png" alt="" /></a></p> <p>It’s amazing how a relatively simple concept map can lead to such a complex database architecture, isn’t it?<br /> 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 <em>strings</em> typed in by the administrators, whereas it would be much better having dropdown boxes.</p> <p>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.</p> <p>The other reason of why the number of tables is higher than the number of entities in the domain model is that I decided <em>not</em> to use the <span class="caps">ENUM</span> type. Firstly because <a href="http://wiki.rubyonrails.org/rails/pages/HowtoUseSetAndEnumColumns">it’s not handled very well by Rails</a><br /> and also because there’s <a href="http://blog.arabx.com.au/?p=87">a number of reasons</a> why ENUMs should not be used.</p> <p>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 <a href="http://railsexpress.de/blog/articles/2005/11/06/the-case-for-piggy-backed-attributes">piggy-backing</a> here and there. <a href="http://railsexpress.de/blog/articles/2006/05/29/simpler-piggy-backing">Someone</a> already thought about a way of doing this in a more “Model-friendly” way. Perhaps I’ll give it a shot.</p> |