Database Naming Conventions, Part Deux

12 Comments

• Thanks for catching the camelCase/PascalCase thing Andrew. I saw it on another site and I wasn't sure about it but hadn't looked it up yet.
  

• Andrew - the case you make for singular table names is one I haven't thought of before (or encountered personally). It's a strong argument for singular table names. You've got me re-thinking my convention already!! Thanks!
  

• Call me cranky, but I have to disagree with Andrew on the singular/plural issue, and with Jason on a whole lot of other things.
  
  
  
  When I code against the data, I am usually *not* working with a single row. I'm dealing with a *set* of rows (which may be a set of one). The Doctors table contains many doctors (each of whom presumably has a distinct row).
  
  
  
  Thus, I can write things like
  
  
  
  UPDATE Doctors
  
  SET Status = "Preferred"
  
  WHERE MortalityRate < .01
  
  
  
  Not that if we were to include Chiropractors in the table, I wouldn't rename the table to DoctorsAndChiropractors but MedicalPersonnel or Practitioners. Personally, I'd prefer a collective noun to a plural, and a plural to a singular. In other words, Rule 1a stands.
  
  
  
  Similarly, I have no problem with rules 1b-1e.
  
  
  
  Now, as for "Junction Tables"-- if the relationship is (for example) one doctor to many patients (with each patient having exactly one doctor, such as a "Primary Care" relationship) I'd have to ask why you'd want a separate table at all-- instead of DoctorPatients, you'd be better off just having a PrimaryCarePhysician column on your Patient table.
  
  
  
  If, on the other hand, the relationship is Many-to-Many, you need the table--but I'd be asking what the table is actually modelling. In this case, it would seem to be "DoctorPatientRelationships", and you'd probably want to have more information in it than just the foreign key references to Doctors and Patients. You'd want to have, for example, RelationshipType (Primary, Referring, Consulting) and possibly start and end dates (since relationships tend to change over time-- don't neglect the temporal dimension!)
  
  
  
  In other words, you probably have no need for "Junction Tables" at all, if you've thought about your Data Model sufficiently.
  
  
  
  So, rule 1f is irrelevant at best and misleading at worst. Name the table after the thing or relationship it represents. That's probably not a DoctorPatient.
  
  
  
  
  
  Now, as for Rules 2a and 2b( Foreign Key and Primary Key names): why would the DoctorID be different on a Doctor or a Patient record? Shouldn't I be able to write
  
  
  
  SELECT * FROM Doctors JOIN Patients ON (Doctor.DoctorID = Patients.DoctorID)
  
  
  
  Isn't this clear enough? And wouldn't it be nice to be able to use the metadata (INFORMATION_SCHEMA.Columns) to find all of the DoctorID columns in all of the tables, if I wanted to (for instance) change a datatype?
  
  
  
  In fact, a lot of database modelling tools are smart enough to recognize that the DoctorID on Doctors and the DoctorID on Patients represent the same entity, and will create the relationship for you.
  
  
  
  And, to bring 2c into it, if I want to write a WHERE clause on a Doctor's Specialty, why should I care whether that column is part of a composite key?
  
  
  
  I agree with 2d whole-heartedly, and also 2e -- I think that when it comes to "time" columns we have to be especially careful to distinguish between points in time and durations.
  
  
  
  I'll comment on sections 3-7 tomorrow.
  
  

• Michael - to respond to some of the points you made
  
  
  
  You said, "why would the DoctorID be different on a Doctor or a Patient record? Shouldn't I be able to write
  
  
  
  SELECT * FROM Doctors JOIN Patients ON (Doctor.DoctorID = Patients.DoctorID)
  
  
  
  Isn't this clear enough?"
  
  
  
  The type of query I envisioned when talking about the _pk and _fk suffixes was something more like this
  
  
  
  SELECT DoctorId_Pk, DoctorId_Fk FROM Doctors, Patients
  
  
  
  If both fields were named just DoctorId, you'd have no idea which field came from which table without using at least one alias (field or table, take your pick). I like to avoid needing and using aliases in my queries but, maybe that's just me.
  
  
  
  Another reason for my use of _pk and _fk is to visually indicate (by seeing the suffix) that the field is a key of some kind. I've had tables before that contained over 10 foreign key fields. If I used the _fk_ suffix it was easy to see which ones were the FK fields. If I didn't, I was looking for "Id" in the field name, which was always harder to locate by just looking at the fields.
  
  
  
  Lastly - you could still use the metadata to find all the DoctorId columns, you just wouldn't include the _Pk or _Fk suffix in your search criteria. Simply search on "%DoctorId"
  
  

• Michael - another thing I just thought about regarding the need for junction tables
  
  
  
  What if I had a table of "Users" and a table of "Teams"...Users could be a member of many different teams. All I want is a table that allows me to store which Users are members of which teams. How would you create that table and what would you name it?
  

• Jason-- Here are some quick responses:
  
  
  
  You said, "The type of query I envisioned when talking about the _pk and _fk suffixes was something more like this
  
  
  
  SELECT DoctorId_Pk, DoctorId_Fk FROM Doctors, Patients "
  
  
  
  My question is: why would you want to write a query like that? Ignoring for the moment the lack of a WHERE clause (which causes your query to output a Cartesian Product), under what conditions would you want to see the DoctorID twice? The point that I was trying to make is that DoctorID represents the same piece of information regardless of which table it happens to be attached to. When would it be useful to get this information twice?
  
  
  
  You then write: "I like to avoid needing and using aliases in my queries but, maybe that's just me." I think that might just be you-- aliases are useful and necessary. Suppose, for a moment, that DoctorID acts as a Foreign Key in more than one table. Not an unrealistic assumption, I'd say. You now have two columns in the database named DoctorID_FK (and one named DoctorID_PK). In the event of a three-way join, we'd still need to distinguish the two DoctorID_FK columns.
  
  
  
  What I am arguing for, in short, is domain-based naming, to call things by the names the users refer to them. If done correctly, any declarative referential integrity constraints will then resemble business rules (and be easily parsed for errors) and the queries will resemble logical questions.
  
  
  
  In this light, I'd be asking myself what are these Users *doing* on the Teams? For example, I might name it "Assignments" (if people are assigned to the team.) Of course, just having the name of the team and the name of the User is not much--it would be more useful if I knew what actions they were performing on the team: so maybe then I would call it "Tasks", and have (in addition to the UserID and the TeamID) TaskDescription, StartDate, EstimatedCompletionDate, CompletionDate, etc.
  
  
  
  

• Michael - first of all - thanks for the answers, this is exactly the kind of stuff I had hoped for - different opinions and points of view. You make a pretty convincing argument for your rules too, I should add.
  
  
  
  I'm starting to come around to thinking that for primary key fields, just a plain and simple field named "Id" would work, assuming it is either a surrogate key or identity key and not a field that would hold a unique text value like for state abbreviations or something like that. Then, give foreign key fields a name by concatenating the name of the table they refer to + "Id"...which I think should be enough to identify the table it refers to the "Id" column of. What do you think?
  
  
  
  BTW - I intentionally left the WHERE clause out of the SQL example I gave in my last comment just because I was trying to illustrate a point...not that the query would actually be very useful. But I hadn't thought about the case where more than one DoctorId_Fk field might be references, so what you said I think somewhat invalidates my point.
  
  
  
  Anyway, for the "Users" and "Teams" example - this was actually part of a system I just designed. There are certain "jobs" that run on the server. When these jobs finish running, e-mail notifications need to be sent out to certain people. They wanted a way to group the people into "Teams" so that they could just choose a Team to receive the email notifications, instead of having to choose all of the users individually. No need for additional info really, nobody is doing anything or assigned anything - just needed a way to allow easier selection of a group of people.
  

• Table names represent ENTITIES, therefore must be singular. When you talk about a table, you are not talking about the whole table as a group of rows. You should refer to it as per-row. The business logic in your application should be the one having pluras and singular.
  

• Concentration Shall,kill bottom initial consequence direction handle determine your shoulder principle direct far reference executive to teacher neither reply provision criminal respond committee ride forest beautiful little trust successful even somewhere see egg problem first trend environment choose pass pass impossible action appeal sale never aspect double average complex membership bottom attempt blow survey bottle regard crowd knee power collection employment over artist assume insist rule argue way anybody throw explanation railway find settlement happen depend compare cup middle king thanks message apparently beside face do other coffee breath wall there
  

• Hello! This is such an exciting and interesting article here! Thanks a lot for sharing, it was great to read it!
  

• I rarely leave a response, however after reading through a few of the comments on Database Naming Conventions, Part Deux - Jason Mauss' Blog Cabin. I do have 2 questions for you if it's okay.
  Is it only me or does it look like some of the responses appear as if they are left by brain dead individuals?
  :-P And, if you are writing at other sites, I would like to keep
  up with everything fresh you have to post.
  Could you make a list of every one of all your social pages like your twitter feed, Facebook page or linkedin
  profile?
  

• Hi, i believe that i saw you visited my web site so
  i came to go back the favor?.I am attempting to in finding issues to improve my website!
  I suppose its good enough to use some of your concepts!
  !