A Database Design Test

I was watching a presentation recently on database design and the speaker talked about how he hires developers. These are full stack developers, for whom the database is a portion of their responsibility. One of the tasks he gives them is a short database design test, designed to get a rough idea of their knowledge of databases.

The test is a short story, with a classroom/course/scheduling scenario. There are descriptions in some business terms, and the instructions ask the reader to to decide how to put entities together and link them. There is a sample unlabeled diagram with only a couple boxes. The diagram is meant to clue them in to the way to indicate relationships, and there are names of different structures in the test in bold. For someone experienced in databases, this would seem trivial as the entities are listed in bold, and the test is designed to be completed in 5 minutes. Extra points for not crossing any relationship lines.

I found this to be a nice, short test to gauge a developer’s knowledge. The speaker noted that they didn’t worry too much about time taken, or the exact notation used in the digram. This is mostly a way to measure if an individual thinks in terms of entities and connections. This is part of a few tests used for a basic evaluation of how a developer solves practical problems, and avoids the trivia based examination used by many interviewers.

I was intrigued since I’ve never been really asked to design anything and I’ve had quite a few jobs where that would be a portion of my job duties. No one has given me a scenario and asked me to produce an ER diagram. The most I’ve gotten is some theoretical questions on normalization, or what keys are. I wonder if I’m alone. For those of you reading this, have you ever had a design test of any kind in an interview? Can you disclose the types of questions or scenarios? When were you tested? It would be interesting to see if this is used by much of anyone.

There seem to be so few ER diagrams in the real world, especially from vendors, who should always produce one for clients. I suspect that few people understand them, or even write them, even though they can be invaluable when trying to write reports and understand the relationships between different entities. Many ask, but could those people produce one? Or read it beyond realizing which field in table a connects to which field in table b? Let me know this week if you’ve been tested on your database design skills.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 4.1MB) podcast or subscribe to the feed at iTunes and Libsyn.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Editorial and tagged , . Bookmark the permalink.

6 Responses to A Database Design Test

  1. Cindi G says:

    I do test candidates on design but not via diagramming. They are presented with a simple flat dataset and asked to design and build the tables to hold that data according to whatever they think are best practices. This allows me to see how they think of relationships, as well as whether they have enough facility with T-SQL to write executable code that includes things like primary and foreign keys in a short amount of time. It’s a bit of a goldilocks test in terms of their not being one right answer but some are more efficient and robust than others. This is the first question on the test and every developer, entry level to senior, who gets the job had to have come up with a decent answer to it.

    • way0utwest says:

      I think that’s fair, as there often isn’t one right answer. And the answer I’d go with often depends on how the application works and might evolve. I have to make some guesses on what is the best structure for data, and I agree, I prefer to see how others work out the process.

  2. Rigerta D. says:

    I have been tested on this when going through an exam for getting accepted on what later became my first job as a developer-that was shortly after getting my bachelors degree and mostly newly graduated students were tested. I think that is the basic question interviewers put on an exam which requires general programming knowledge and basic database knowledge, to kind of get a grasp of the candidates database knowledge as well.
    The scenario, if I remember well, was designing a schema for a horse-racing application, having to define entities and relationships between the horses, riders, races and race results based on some short description as a business requirement.

    Funnily enough, when interviewed later on for a SQL Developer position, I was not asked of this. The questions get much more specific I think, based on the job – if it’s mainly a database job, then interviewers jump to some “more complicated” stuff like heaps vs clustered tables, indexes, statistics, different database objects, query execution plans and so on!

  3. Chris F. says:

    I once had an interview that was similar, but it was more of a SQL programming test than a diagramming test. The interviewer had a fully fleshed out diagram and some sample data and then asked a series of 10 business-related scenarios for which I had to build the queries. There was also a few quick questions related to database terms and subjects, things like “What are the two main types of indexes in SQL Server and how are they different?” or “What is the four part naming in a query?”. I thought it was a really good way to show my knowledge and to prove that I could do what I said I could, and I wish other companies would interview this way.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s