22 October 2013

Questions Arising from First Database Design Quiz

[This post is from Chris Saxon, manager of the weekly Database Design quiz.]
The database design quiz for the 12th-18th October covered implementation of  a preferred address feature for customers. The first choice in the answers added a DEFAULT_ADDRESS_ID column to the customers table. 

This answer was marked as correct, however many players selected this choice as incorrect and a few objected on the basis that no foreign key was defined (on the DEFAULT_ADDRESS_ID column). 

The requirements in the question stated that customers could select "at most one" of the addresses to be their preferred address. By definition the PLCH_CUSTOMERS.DEFAULT_ADDRESS_ID meets this, as it's only possible to store one default address per customer. In my opinion, adding a FK to this column to an address table while a very desirable action isn't strictly necessary to meet the "at most one" requirement. 

This also brings up a wider concern I have with these quizzes regarding foreign keys. This week's quiz (18th-25th October 2013) includes a CURRENCY_CD field. If I was designing "for real", there would definitely be a FK on this column to a CURRENCIES table. I excluded the FK and additional table from the question however to keep it shorter and easier to read. There are a couple of other "missing" FKs and tables from the question, which also aren't strictly necessary to answer the question. 

Based on the response to the default address in the previous quiz, some players may feel these are necessary for some of the choices to be correct. 

So I would like to gather your thoughts on how we should handle these issues. 

The questions I have are: 
  • If a quiz includes a choice with fields that reference a key from another table, is it necessary to define a FK for the choice to be correct? (If the question states that we must do something, for example to use valid addresses, I certainly agree that a FK would be necessary) 
  • In general, should questions include as many FKs and tables as possible? Or should FKs only be listed where directly impact the correctness of some of the choices? 
Please let us know your thoughts on this!

Also, remember that we will not publish any comments that reference the details of this week's quiz! No spoiler alerts!

Feedback Needed: A new vision for the PL/SQL Challenge

We started the PL/SQL Challenge in April 2010 with a daily PL/SQL quiz. Over the years since, we have added weekly APEX, SQL and Logic quizzes, as well as monthly quizzes, quarterly championships and much, much more: over 1,200 to date!

Through it all we published five new quizzes on PL/SQL each week, come rain or shine, holiday or workday. And it should come as no big surprise, given that this whole site was my idea (Steven Feuerstein), that I have written more quizzes than everyone else combined: over 1,000. Wow.

I've gotten pretty good at it (productive, that is) and I actually do enjoy it. 

Yet I now feel that the time has come to end the daily quizzes. I know that many of you may have strong feelings about this, so I would like to share (a) my reasons for feeling this way and (b) what I would like to do in place of the daily quiz.

I plan to put these changes into effect on 1 January 2014, so I very much want your feedback.

Here are my reasons:

1. It's very time consuming to write five quizzes per week, along with verification code and resources. It's also quite a job to review all these quizzes, and much of that effort has fallen at this point to a single, valiant player: Elic.

I have greatly enjoyed writing these quizzes. I've learned even more about PL/SQL in the process and, certainly, feel good about the impact of these quizzes on thousands of developers around the world.

But the bottom line is that by committing so much of my time to these quizzes, I have that much less time for other activities, both related to Oracle technologies and completely distinct from them.

And I must tell you that whether it is a mid-life crisis (I just celebrated my 55th birthday) or just one more human being coming to his or her senses, I feel more and more strongly with each passing day that I need to spend more time out in the natural world, away from computers and the Internet and plastic.

2. It's quite a burden on our reviewers to ensure a high quality for five quizzes each week. For a while, I had four-five PL/SQL developers checking my quizzes (well, usually 2-3 for any given quiz), but lately the main responsibility has fallen on our most excellent and diligent reviewer, Elic. He shows no signs of wear and tear, but it worries me....

3. Hmmm. I guess that's really about it. :-)

So what would I like to do instead? (with the PL/SQL Challenge, that is. Check out feuerthoughts.blogspot.com for occasional disclosures about what I plan to do with the rest of my life, overall. My views on that have been changing a lot in the past year.)

Here's what I am thinking about right now:

Offer THREE weekly quizzes on PL/SQL, as follows:

1. The PL/SQL Challenge: a weekly quiz authored by me that covers the gamut of PL/SQL features, as we have been going with the daily quiz - just once per week. See? I am not going to stop writing quizzes entirely!

2. The Community PL/SQL quiz: a weekly quiz that is authored by others (anyone but Steven Feuerstein). Currently we restrict how many questions authored by players can be used per quarter to just three. At least one player (Iudith Mentzel) has written lots of great questions that could be played and appreciated better with this new weekly quiz. And I hope that having a weekly quiz specifically showcasing the knowledge and creativity of others will encourage you to submit your own!

3. Deja Vu PL/SQL: a weekly quiz that features a question previously played in the daily PL/SQL quiz. Of course, if you spend lots of time practicing past quizzes, these will, over time, get ridiculously easy for you, but hey there's nothing wrong with that. You are internalizing programming patterns, leading to improved productivity and code quality in your own work. I am certain of it!

The PL/SQL Challenge quiz will be the primary quiz for rankings and qualifications for championships (more on that below). We would also rank the Community PL/SQL and Deja Vu PL/SQL quizzes, but they would not be used directly to qualify for championships. Instead, some players of these quizzes would qualify through a variety of special rules (play all quizzes in a quarter with N% correct or higher, that sort of thing).

Keep the Quarterly PL/SQL Championship

One could argue that there will no longer be enough quizzes in a quarter (12 weekly vs roughly 60 daily) to establish clearly who should participate in the championship. I was thinking about switching to a semi-annual (every 6 months) championship, but that complicates matters in our backend (support for a new frequency) and I don't really want to give players any fewer reasons to think about and visit the site.

We will probably adjust the rules around what it takes to qualify for the championship. And we will continue to play at a single time, to ensure the integrity of the competition.

Add a New Annual PL/SQL Championship

We are adding annual championships for SQL and Logic in 2014. We will do the same for PL/SQL, maybe starting in 2014 or wait until 2015.

Still sorting out the rules for participation. Maybe we will "loosen" things up and make it an "open" contest: anyone can play!Or have an open qualifier: take three quizzes (everyone at the same time), top 50 play in the annual championship.

Set Up More Tests
We recently rolled out the new Tests feature, which currently gives you the ability to take a past PL/SQL Quarterly Championship under the same conditions as the original players.

We will add more tests with specific feature area foci, such as a test on dynamic SQL or bulk processing. These will consist of multiple quizzes (not from those already played) that are designed to test how thoroughly you understand a particular area of functionality, and will include a mix of code-based and word quizzes.

Build the Community

I spend so much time writing quizzes, I don't have enough time to more fully develop the community side of the PL/SQL Challenge. I would like to start highlighting player accomplishments (Player of the Year, Reviewer of the Year, etc.), as well as provide better-organized and fully-realized utilities and reusable code.

And more generally I want to make the site less about/from me and more about featuring the knowledge and experience of PL/SQL experts all around the world!