June 30, 2017 at 8:24 am
Wow, I didn't realize my experience was so contrary to the norm. My test for the job I have right now had me write an ER diagram then write a stored procedure to put into a website, and in my job I've had to do a lot of ER designs and get them verified by teammates before implementing them.
We use ER diagrams for communicating everything about our current databases, and for talking about designs; how else would you do it?
I guess it's possible that my experience comes from necessity; we have huge legacy systems that no one understands anymore, since everyone who wrote it has left over the course of about 3 years. And worse, most of the tables were imported from Access without constraints, so we can't even use database visualization tools to just see where everything's pointing. We have had to do a lot of reverse-engineering from SPs to figure out what's supposed to be tied together. So yeah, diagrams are something we can't live without.
June 30, 2017 at 8:32 am
Yes, I was asked to produce a database design for a simple scenario, such as you described. This was back when I was newly laid off. I'm sorry, but I don't remember the exact scenario. Because I was newly unemployed and that period of time lasted longer than I expected, I had a lot of interviews (mostly by phone) do I just don't remember the details anymore.
And, FWIW, I didn't get the job. Perhaps I did a bad job of designing the database.
Kindest Regards, Rod Connect with me on LinkedIn.
June 30, 2017 at 8:32 am
podmate - Friday, June 30, 2017 8:24 AMIMHO, developers have no business building DB databases and I say this as a former developer. I also feel this way about developers writing SQL in general.
I can't tell you the number of times that I have had to put my foot down to stop a developer trying to mangle the data in a DB simply to make his/her life easier on the application side.
I can't tell you the number of times that I have had to 'consult' on terribly designed DB's (all designed by a 'full stack' developer).I would love to have a DB design test in the interview process, but the heads of development would never let this fly.
Following your advice I should never be allowed to design a database. But to follow your comment further I have known and worked with a LOT of so called architects and DBAs who had no business designing a database. Your view is extremely narrow and prejudiced. I am what you would call a full stack developer and I think I would do a fine job designing nearly any database. I am sure that some of the folks around here would agree. Don't be so quick to judge based on somebody's title or even role.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 30, 2017 at 8:42 am
Sean Lange - Friday, June 30, 2017 8:32 AMpodmate - Friday, June 30, 2017 8:24 AMIMHO, developers have no business building DB databases and I say this as a former developer. I also feel this way about developers writing SQL in general.
I can't tell you the number of times that I have had to put my foot down to stop a developer trying to mangle the data in a DB simply to make his/her life easier on the application side.
I can't tell you the number of times that I have had to 'consult' on terribly designed DB's (all designed by a 'full stack' developer).I would love to have a DB design test in the interview process, but the heads of development would never let this fly.
Following your advice I should never be allowed to design a database. But to follow your comment further I have known and worked with a LOT of so called architects and DBAs who had no business designing a database. Your view is extremely narrow and prejudiced. I am what you would call a full stack developer and I think I would do a fine job designing nearly any database. I am sure that some of the folks around here would agree. Don't be so quick to judge based on somebody's title or even role.
All of this is based off of my experience over 20 years. Harsh? yes. But in my experience true.
I have worked with precious few developers who were competent in anything DB/SQL.
Of all the dev's that I currently work with, there is only 1 that I would trust to build their own DB structure.
Dev's that are well versed in both programming and DB/SQL are few in far between in my experience.
I would also say that DB/SQL people who are great programmers are also few and far between, in my experience.
I am very willing to let a dev give design a try. Both of us can learn from this exercise.
But, I have found few dev's who were interested in learning how to design beyond finding ways to make their coding easier (beyond all else).
June 30, 2017 at 8:58 am
podmate - Friday, June 30, 2017 8:42 AMSean Lange - Friday, June 30, 2017 8:32 AMpodmate - Friday, June 30, 2017 8:24 AMIMHO, developers have no business building DB databases and I say this as a former developer. I also feel this way about developers writing SQL in general.
I can't tell you the number of times that I have had to put my foot down to stop a developer trying to mangle the data in a DB simply to make his/her life easier on the application side.
I can't tell you the number of times that I have had to 'consult' on terribly designed DB's (all designed by a 'full stack' developer).I would love to have a DB design test in the interview process, but the heads of development would never let this fly.
Following your advice I should never be allowed to design a database. But to follow your comment further I have known and worked with a LOT of so called architects and DBAs who had no business designing a database. Your view is extremely narrow and prejudiced. I am what you would call a full stack developer and I think I would do a fine job designing nearly any database. I am sure that some of the folks around here would agree. Don't be so quick to judge based on somebody's title or even role.
All of this is based off of my experience over 20 years. Harsh? yes. But in my experience true.
I have worked with precious few developers who were competent in anything DB/SQL.
Of all the dev's that I currently work with, there is only 1 that I would trust to build their own DB structure.
Dev's that are well versed in both programming and DB/SQL are few in far between in my experience.
I would also say that DB/SQL people who are great programmers are also few and far between, in my experience.
You will find that a lot of the good folks around here are developers. Certainly not all of them but far more than you might expect. Going on your original comments there was no concession at all that anybody who is a developer should be allowed to design a database. But in your last comment you admitted there might be some. I don't have 20 years experience yet but this is my second career and I am pretty close to 20 years now. I too have worked with pretty few out and out devs that can design a good database but that doesn't mean that none of them can. Perhaps I took exception to your comments because it was so stark with no exceptions. I am one of those exceptions and there are many more around here. The problem happens when good people such as yourself assume that somebody who is a dev is not capable of something. I like to base these kinds of things individually with the person, not on their job title.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 30, 2017 at 9:10 am
podmate - Friday, June 30, 2017 8:24 AMI would love to have a DB design test in the interview process, but the heads of development would never let this fly.
I have a lot of opinions about which department should run a software company, but this is a new one for me. Any developer who doesn't follow the guidance of DBAs is a nutjob.
June 30, 2017 at 9:59 am
The department I left yesterday is hiring new datamodellers so my colleague drafted a small test, where the candidate had to reverse engineer a single table with 5 lines of data into a very small model. It could be modelled in several ways depending on the assumptions you had to make. We were actually interested most in those assumptions, not in a "correct" model because there really wasn't a single correct model.
The first two candidates failed abysmally, despite being great modellers (in theory). Apparently, they were used to designing logical models based on interviews - at least, that was the excuse of one of them. We failed to see why that made it impossible to reverse engineer a simple table into entities. But we have high hopes for the next pair 🙂
That same colleague once hired me on our previous job based on another scenario, where I had to design a DataVault model and explain it. Went pretty well, but apparently I was the only person to get it right.
Personally, I'm very fond of tests done right. But if people start checking things I would normally just google (T-sql syntax, for instance) or use snippets for, I'd probably fail as well. So i'm not for testing when the people themselves don't really know how to test. Having mediocre developers create tests will just lead to mediocre developers getting hired.
June 30, 2017 at 10:14 am
GeorgeCopeland - Friday, June 30, 2017 9:10 AMpodmate - Friday, June 30, 2017 8:24 AMI would love to have a DB design test in the interview process, but the heads of development would never let this fly.I have a lot of opinions about which department should run a software company, but this is a new one for me. Any developer who doesn't follow the guidance of DBAs is a nutjob.
This is what happens when management is solely developer focused and has been for a few years.
DBA's have been marginalized to basically applying patches, keeping the databases alive and documentation specialists. It sucks!
Fortunately, I am not a DBA (although I have been in the far distant past).
The division is feeling the pain of being mis-managed for so long and so many of my warnings/predictions have proven to be true, that I have finally got some in management to listen to me. Not saying that I am that great of an architect, but I have been there and done that often enough to have learned some painful lessons. My goal is to bring the DBA's in out of the cold and to strike a balance between database best practices and the dev's needs.
July 1, 2017 at 4:18 am
Almost all of my database related interviews have been the same typical questions and trick questions about functions, indexing, backups, and so forth. More or less looking to know if I've retained all of these definitions in my head versus looking them up on Google. Pretty useless interview questions if you ask me.
The closest I've been to ERD would be for a non-database related position. It was for a full-stack developer position with Ruby on Rails. The question was around designing a online store system and how I would design the classes and relationships. It was followed up by questions around recursion such as how could I replicate the fibonacci sequence in code as well potentially SQL.
They were trying to gauge my ability to be object oriented with the class structure, how I would formulate relationships between attributes, and how I would tie it to a proper hierarchy between great grandparent, grandparent, parent, child and so forth.
While not database related, it's pretty close to what I do today with data architecture, just with a different set of rules due to the database systems I work with.
July 3, 2017 at 8:43 am
rabradford - Friday, June 30, 2017 2:30 AMThe normalisation then ERD then simple querying test is something we use as a norm in our interviews for DB bods including DBA's. We give them a simple form of data and ask how you'd normalise it (whatever NF they want, but usually 3NF), then we ask them to draw up a quick ERD based upon their conclusion and also to add indexes, then given the ERD we ask them how you'd retrieve and/or aggregate various records (left join, inner join, sum etc). All fairly simple. This is a too and fro conversation and we will certainly add explanations, discuss why and give promptings if necessary. This gives a us set of discussion points but we do take care to be fairly easy-going so it becomes more of a conversation. We don't expect the world from the interviewees but we do expect some basic understanding. As you can probably guess.. most interviewees flounder hopelessly, but then we know it is hard to think on your feet and interviews tend to be stressful so we do make allowances for this.
If you want, this would be a great short article, give a test and then a solution. Might help people learn.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
July 3, 2017 at 8:44 am
Heals - Friday, June 30, 2017 3:27 AMSlightly different take on a similar theme...We recently interviewed for a couple of positions that would involve developers feeding our data warehouse. As well as the usual prescribed HR questions (Some of which we didn't really hold in high regard as they were relatively meaningless for the jobs we needed to fill) I created two tests that asked basically the same questions.
I created an 'Interview' DB, filled out some dummy data (Only a small number of records, with no 'gotchas' - we weren't trying to make people fall over in an already stressful situation) and asked a number of questions whereby they were given the exact fields required and any summarising that should occur. I provided a DB diagram so they could see how everything linked together if it wasn't immediately apparent.
The other way that I did it was to print out the data contained within the tables (Albeit with lookup ID's replaced with the actual values etc.) and asked the same questions, but asked to show how they would link the tables together and what the expected outcomes would be.
We were more interested in finding out if people's heads worked 'the right way' when it came to navigating a predefined structure than testing their T-SQL coding skills as this can be taught relatively easily as long as the thinking is right.
Happily both candidates came in and hit the ground running after some initial training, so it seems to have worked in our case!
This would be a great article as well, if you'd like to write it.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
July 3, 2017 at 8:47 am
hammackk - Friday, June 30, 2017 8:24 AMWe use ER diagrams for communicating everything about our current databases, and for talking about designs; how else would you do it?
very poorly
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
July 3, 2017 at 1:54 pm
The following is not really a database design question, but more of a baseline SQL query question. It's simple enough that a core SQL developer could easily rattle off the answer without thinking about it too hard, while completely stumping a candidate with little SQL experience. I don't expect them to actually write a perfect SQL statement, only to verbally describe the keywords and clauses required to solve it. It also tests their ability to think through a relatively common relational data problem on their feet, because that's what experienced database developers are expected to do on the job from Day 1.
For a more advanced written exam, a variation on the question might include a join between Customer and CustomerContact, but I don't want to clutter any one question with too much complexity, and if they know how to write a GROUP BY query, then I'm willing to trust they they also know how to JOIN. I'll have additional questions regarding various types of inner, outer, and compound key JOINs.
Let's assume we have a table called CustomerContact which contains two columns: CustomerID and ContactDate. I want you to write a SQL select statement that returns one row for each customer who was contacted (2) or more times within the month of November 2015.
The columns returned should be: CustomerID, the total number of times the customer was contacted within that month (ContactCount), and the last contact date for the customer within that month (LastContactDate).
Below is the sample data you'll be working with:
CustomerID ContactDate
28 2015/10/23
35 2015/10/23
40 2015/10/30
28 2015/11/01
35 2015/11/03
35 2015/11/10
40 2015/11/28
35 2015/12/01
40 2015/12/15The expected outcome is as follows:
CustomerID ContactCount LastContactDate
35 2 2015/11/10
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
July 3, 2017 at 1:56 pm
The expected answer would be similar to the following, although any equivalent and simple set based solution would suffice.
SELECT CustomerID
, COUNT(*) AS ContactCount
, MAX(ContactDate) AS LastContactDate
FROM CustomerContact
WHERE ContactDate >= '2015/11/01'
AND ContactDate < '2015/12/01'
GROUP BY CustomerID
HAVING COUNT(*) >= 2;
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
July 3, 2017 at 2:59 pm
Yes, I've been given a test as a requirement for a job before. I'm not trying to boast, but I found it to be quite simple. I'm sure there are rough ones out there, but this wasn't it. I've also heard of some questions given in interviews by people on this site and remember a link to a page full of bad questions. Some of them were horrible and others were just flat-out wrong, but the author wasn't interested in feedback. I can't find that topic any more, but it would only serve to make everyone's eyes bleed.
I think some of the best interviews are conversational in nature. Yes, competency is important, but assessing personality and how someone thinks about a problem is important as well. So much of what we do is problem-solving and that's something that I think would be difficult to assess from a multiple-choice test.
Viewing 15 posts - 16 through 30 (of 35 total)
You must be logged in to reply to this topic. Login to reply