Feb 272015

Following the database design championship playoff over on the PL/SQL Challenge, one player asked:

“Could you please advise me some resources (boooks, courses) so that I can improve?”

Of course I can! This is what I love to hear – people looking for resources they can use to increase their skills. This is information I’m sure others will benefit from, so here goes:

select materials
from   my_readings
where  topic = 'Database design';

I’ve found The Database Programmer blog to be immensely helpful for explaining normal forms and constraints. This provided the inspiration for the “shift patterns” question using the school class schema developed aross the “Table Design Patterns” series.

Another fun paper for explaining normalization is “Data Normalization, Denormalization and the Forces of Darkness

Many people struggled with the orders “physical co-locating data” question. The inspiration for that question came from a number of places:

* Martin Widlake’s IOT series
* Iggy Fernandez’s “No to SQL and No to NoSQL” which has an example using table clusters at the bottom. His “The Twelve Days of NoSQL” series on the same blog also provides great ideas.
* The Data Warehouse Insider Blog article on attribute clustering

One key thing to remember is that physical data location can matter hugely for database performance. Unfortunately this is one area that people often overlook. The lack of index-organized tables in most Oracle databases is a great example of this.

To understand more about indexing – particularly across different database platforms – Use the Index, Luke! is a great site. If you really want to understand indexes in Oracle, take a look at Richard Foote’s blog (note – this does get very detailed!).

Of course, no list about designing for Oracle would be complete without Tom Kyte’s books. Reading these helped me understand how Oracle functions.

Finally I have to wrap up with a quick nod to the Oracle docs. Not exactly bedtime reading, but skimming through topics there has provided inspiration for a number of questions on the PL/SQL Challenge. :)

Of course this isn’t a complete list (that would span many pages!). These are some of the resources I’ve found most useful (and refer back to often).

So I’d like to hear from you. Which resources have you found most valuable when it comes to learning about designing (Oracle) databases? Please share your thoughts in the comments – I’d love to hear what other people have found useful.

Nov 042013
select my_thoughts
from   starting_the_db_design_quiz;

As regular readers will know, last month saw the start of the Database Design Quiz on the PL/SQL Challenge. I thought I’d share some of the background on how this came about and my thoughts and feelings on how it’s going, so here goes…

I’ve been player of PL/SQL quiz for a few years now. Writing code is only a small part of my current day job however – as a “Data Architect” more of my time is spent designing data structures (though I still get called on to help coding from time to time!). With this in mind, I’d started drafting up some questions for the challenge that were more related to my work. These didn’t seem to fit with the PL/SQL quizzes though. While there’s clearly some cross-over, it didn’t feel quite to have these in the PL/SQL or SQL quizzes.

So one night the summer while trying to get my daughter to sleep, I had a crazy idea – wouldn’t it be great to have a whole new type of quiz on the PL/SQL Challenge? It could be based around the theme of database design: creating and normalizing data structures and then the physical implementation of these. With this in mind, I drafted up some questions and sent them over to Steven Feuerstein.

Steven really liked the idea, but suggested that we gather feedback from players before going ahead with it. So we put together a post for the PL/SQL Challenge blog including some sample questions. I was amazed by the positive reception it received – clearly this was something many people felt was worth doing.

With this support clearly we needed to go ahead so I set about writing quizzes. I’d been thinking about possible questions for a while, so I had several ideas lined up and submitting questions has been (relatively) easy so far. I suspect generating new ideas will get harder in time though – I’m really impressed that Steven and Kim have been coming up with new questions for as long as they have!

In addition to writing the questions, I needed to find some people to help review them to ensure they’re up to scratch – it’s amazing how easily I’ll manage to miss a bracket or use slighlty confusing language.

I instantly thought of an old colleague of mine, Andy Wilson, who quickly agreed to help :) I also feel privileged that Kent Graziano – an Oracle ACE and published author on Data Warehouse modelling – agreed to help review questions. They have both provided some excellent feedback on the questions so far. I’m sure the improvements they’ve suggested are part of the reason the quizzes are getting good scores in post-quiz surveys. They’ve tidied up the language and pointed out some flaws in the questions.

With Kent and Andy on the job and a batch of quizzes ready, we settled on a launch date of 5th October to coincide with the start of a new quarter in the PL/SQL & SQL quizzes. As the launch date got closer got more excited and nervous. Would it all work out well? Would people play? Being based in the UK, the new quiz would start at midnight my time. I’d love to say I stayed up to see it start, but having a daugher under 1 is taking its toll and I can barely stay up past 9pm these days ;) I was up first thing on the 5th however (only partly becuase Izzy was already awake!), checking that the quiz was working correctly. Other than a slight typo in the question, it was a relief to see it live and functioning as expected.

So we’re now a month into the quiz and I feel like it’s going well – there’s only been one major disagreement so far and around 400 players per quiz, which is great. In all I’m happy with the way it’s going at the moment and feel really honoured that so many people have taken part. Hopefully this will continue you to grow – but for that part I’ll need help from you: by playing, adding your feedback to quizzes and spreading the word about this quiz we can help grow the community (including the PL/SQL, SQL and Logic quizzes too).

For those of you who’ve been taking part in the DB design quiz, I’d love to hear how you think it’s going. Are the questions interesting and varied enough? Are they too easy/hard? What have we not covered (yet) that you think we should? Please share your thoughts in the comments!

Apr 152013
insert into when_you_can_have_columns (
) values (
  'Joins are a #!@% to write',
  'Aggregation functions are a pain',
  'You''re not in 1st normal form',
  'You''re limited to 1000 values',
  'To add more reasons needs a schema change'

A similar design flaw is to implement rows as a column separated list "overcoming" the final two limitations.
However, they introduce the costs of everything is a string and having to do messy substrings to extract the "rows".
Apr 082013
select benefit
from   normalisation
where  benefit not in (
    'Preventing update anomalies',
    'Ensuring uniqueness of rows',
    'Supporting (unknown) ad-hoc SQL queries',
    'Allowing extensions to the data model',
    'Representing entity relations',
    'Improving consistency by avoiding redundancy'

no rows selected

Apart from all that, there's no benefits right?! ;) I'm sure there's more than those listed; 
if you've got any to add just put them in the comments and I'll update the post