7 ms·
I do a lot of interviews for software developers, and I ask a very simple database normalization question that close to half of the candidates miss completely.
by novum 16y ago
I do a lot of interviews for software developers, and I ask a very simple database normalization question that close to half of the candidates miss completely.
Something like: "You're tracking doctors, hospitals, and patients. Doctors can see multiple patients, and doctors can staff multiple hospitals". All I'm looking for you to say is that you'd put junction tables between the main entities.
It's so, so crucial that developers understand the basics of database design, and normalization is a huge part of that.
- kingkilr 16y agoGah, I've been in this business too long (I'm 19, WTF?), my first question is, can doctors be patients as well?
- ciupicri 16y agoInteresting question, but does it really matter? You're not tracking persons in general like a government would do, you're only tracking staff and clients. It would matter that a doctor is also a patient only if he/she would have some kind of preferential treatment, e.g. a discount or a better room.
- kingkilr 16y agoOr if you're storing information about them, say their home address, better to have it in one place than two.
- lolipop1 16y agoNo, simply because they are from different domains and by extension from and for different systems. And as such, a natural ID (or arguably a GUID) can also be said to be more fitting to establish that kind of relation.
- deleted 16y ago[deleted]
- prodigal_erik 16y agoI would strongly urge a doctor who gets sick to either use a pseudonym or go to some other hospital at which he's not listed as a doctor. MUMPS code and its schemaless shared-everything database (for medical records!) is terrifying enough all by itself before you give it edge cases like "this doctor is a patient". http://en.wikipedia.org/wiki/MUMPS http://en.wikipedia.org/wiki/MUMPS
- Confusion 16y agoNo, that would not be a good thing to support. There can be Doctors and Patients that refer to the same real person, but you don't normalize these entities with respect to a single Person column. It's just not worth it, because you too rarely care that a Doctor can also be a Patient. In such a case, if it poses a problem (only when Patient is assigned himself as Doctor?), it will sort itself out in The Real World.
- nradov 16y agoThe HL7 RIM actually allows for this. A Person can have multiple roles including Patient and LicensedEntity (doctor). http://www.hl7.org/v3ballot/html/infrastructure/rim/rim.htm http://www.hl7.org/v3ballot/html/infrastructure/rim/rim.htm
- Confusion 16y agoTrue, this example of doctors and patients is just the wrong sort of example, because that world has already been standardized into oblivion. However, in general, for your relatively small app for company X, you wouldn't go this far. In general, the fact that a supplier can sometimes also be a client is usually not very interesting.
- tbrownaw 16y agoNice sanity test buried in there too, how many candidates try to put a DoctorID field on the patient and hospital tables (ie, assume a patient can only see one doctor, or a hospital only has one doctor working there)?
- Another1 16y agojunction! really! i looked up this word online and only found this http://en.wikipedia.org/wiki/Perl_6#Junctions http://en.wikipedia.org/wiki/Perl_6#Junctions the relational term would be relation, please stop interviewing ppl for jobs you are not qualified
- sandee 16y agohttp://en.wikipedia.org/wiki/Junction_table http://en.wikipedia.org/wiki/Junction_table
- brown9-2 16y agoWouldn't the term "junction" tell you what you need to know about this table? "Junction", "join table", "intersection table" - really, you are going to judge someone's competency based on which flavor of a synonym they use?
- mbateman 16y agoYou "looked the word up online" just now and feel qualified to judge whether or not someone is a qualified interviewer? EDIT: Meh, I have been trolled.
- Groxx 16y agoI hadn't seen the Perl6 junctions before... that's an utterly awesome language feature o_O I really need to take a close look at Perl 6 some time.
- giardini 16y agoWhy downmod him so? His implication that the term "junction table" is nonstandard terminology is correct. Despite 45 years in IT and a good 25 years of relational databases, I don't recall having heard (or read) the term "junction table" in a technical discussion. A cursory Google search indicates it may be recent terminology from the Microsoft corner of the universe.
- Groxx 16y agoIn this case, the many to many relationships should be obvious, but I feel I must point out: Based on that text specifically, all that's needed is that the patient and hospital each have a doctor ID column. How many would get it if you specifically pointed out that they need to consider real-world circumstances in this, and / or if you enumerated all the requirements? Many many many times, when stating a hypothetical question, real-world handling responses are not desirable to the questioners. If the interviewee realizes this, they also realize that they risk losing points no matter how they answer: answering based on the specified requirements fails with you, but answering based on real-world uses fails with people testing against over-engineering / people religious about specific answers to specific questions. And being verbose risks both groups, based pretty much solely on the interviewer, of whom they have effectively no knowledge. Or have you done this, and it just wasn't included in the example above?
- novum 16y agoI usually include some extra details about each entity to imply that each should be its own table.
- Groxx 16y agoAh, sorry, I left that part vague. I meant 3 tables, not just two. The point I was trying to make was that your correct answer required junction tables; other interviewers' correct answers require _only_ doctorID, because that's all your text required. And still other interviewers dock points for answering "cleverly" and covering both bases. Without explicit context, how is the interviewee to know which the interviewer wants? They risk a lower review score no matter how they answer.
- arethuza 16y agoAsking the interviewer for more information? The ability to ask questions is far important than whether anyone can remember the details of database normal forms!
- skybrian 16y ago
- djb_hackernews 16y agodevils advocate, but why are you asking database engineer questions during a software developer interview?
- brunoc 16y agoWouldn't you agree that most programmers will have to use databases and should at least have a basic working knowledge of how they work? Database specialists usually end up with very specific knowledge of a particular platform, for example Oracle where there are endless ways to tweak for performance, or replication or whatever. Modeling with RDBMS is usually very straightforward and ends up being only a minuscule fraction of what you do day to day.
- djb_hackernews 16y agoprogrammers? While I hate the word I would agree some basic working knowledge is necessary. But if you post a job description looking for a php programmer, and then you bring people in and start asking questions about schema design where even a correct answer isn't correct because now you also want your programmer to be a software designer who does requirements gathering as well it's your own fault for expecting to be interviewing architects instead of programmers. If you are looking for someone to do schema design, that damn well better be in the job description given to candidates before they apply, and your software engineers damn well better be compensated for the extra required skill.
- Confusion 16y agoBecause I've never had a database engineer available to create my schemas for me. And frankly, I've never had a problem designing or maintaining a schema: it's not rocket science. Writing queries and getting performance out of the DB, that can be rocket science.
- strlen 16y ago> Writing queries and getting performance out of the DB, that can be rocket science. I tend to ask questions more about database internals, but it's a crucial job for an engineer to understand how to get performance and integrity for their queries. Sometimes times you may get world-class DBA/DBE (my current employer is one of those places) who can do an engineer's job (in terms of tuning the queries, etc...) but that is really an engineer's job.
- timwiseman 16y agoAs a senior DBA that has done many interviews for more junior DBA/database developer, I too have been surprised by how many candidates cannot answer a question like that at all. With that said, many developers (other than database developers) really do not need to understand that. They don't need to understand it because they will normally have a good DBA/database developer that handles that part for them. In fact, large projects may have many specialized DBA roles including Architects focused specifically on schema design.
- Shorel 16y agoIt totally depends on the use you plan for the DB. For a DataWarehouse, you probably want to de-normalize the DB if you expect good performance when calculating those cubes.