Jason Mauss' Blog Cabin

camelCase and PascalCase are not the same.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpgenref/html/cpconcapitalizationstyles.asp

Plural vs Singular names for table names can be a hot topic. I used to believe in plurals, but my experiences changed that.

With Doctors and Patients, DoctorsPatients could be a 1 to 1 or many to many.
With Doctor and Patient, DoctorPatients is one to many while DoctorsPatients is the many to many. Also, when I code against the data, I am usualy working with a single row so one "doctor" rather than one "doctors".

I am a fanatic about the naming of things and SQL seems to be the hardest place to have a clear convention.
When a view and a table are the only public visible things in the DB, should the really be named differently?

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

How are you. The release of atomic energy has not created a new problem. It has merely made more urgent the necessity of solving an existing one. Help me! Help to find sites on the: Dramatic eyelash extensions individual lashes. I found only this - <a href="www.cis-cmc.eu/.../misencil-eyelash-extensions">misencil eyelash extensions</a>. Eyelash extensions, you cannot rise colors to learn many, but about through exact mascara, you can need black ages in a today of peoples. Purchase also both the extensions of eyelashes and the eyelash used before looking them, eyelash extensions. THX :-(, Wapi from Panama.

hola

nordseefan.wordpress.com

bye

http://arno.60.vox.com/

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

 I am assured, what is it — a false way.

P.S. Please review our <a href="www.softcluster.com/.../Milit-rische-Icon-Sammlung_19294_5.html">icons for Windows</a>  and windows13icons.

 You will not make it.

P.S. Please review <a href="popavidi3.deviantart.com/.../Cute-Smile-Ikons-297318195">Cute Smile Ikons from popavidi3</a>

 Please, teol more in detail..

P.S. Please review <a href="designesticons.deviantart.com/.../Retina-App-Tab-Bar-Icons-279442184">Retina App Tab Bar Icons from designesticons</a>

 The matchless theme, very much is pleasant to me :)

P.S. Please review <a href="iconoman.deviantart.com/.../High-Resolution-App-Tab-Bar-Icons-for-iPhone-276470479 Resolution App Tab Bar Icons for iPhone from Iconoman</a>

 I here am casual, but was specially registered to participate in discussion.

P.S. Please review <a href="romavidi2.deviantart.com/.../Android-Dialog-Icons-296677838">Android Dialog Icons from romavidi2</a>

 It is remarkable

P.S. Please review <a href="narutoicons86.deviantart.com/.../Black-Hand-Icons-310728341">Black Hand Icons from narutoicons86</a>

 What magnificent words

P.S. Please review <a href="martinking33.deviantart.com/.../Small-PNG-Icons-311832851">Small PNG Icons from martinking33</a>

 Correctly! Goes!

P.S. Please review <a href="popavidi3nr.deviantart.com/.../16x16-Pixel-Toolbar-Icons-296459271">16x16 Pixel Toolbar Icons from popavidi3nr</a>

 These are all fairy tales!

P.S. Please review <a href="iconjarcom5.deviantart.com/.../32x32-Music-Icons-310702812">32x32 Music Icons from iconjarcom5</a>

 Excuse for that I interfere … To me this situation is familiar. Let's discuss. Write here or in PM.

P.S. Please review <a href="tsenitelikon.deviantart.com/.../Small-Web-Icons-284221993">Small Web Icons from tsenitelIkon</a>

 It is very valuable phrase

P.S. Please review <a href="myiconmyicon92.deviantart.com/.../Email-Icon-Set-311157917">Email Icon Set from myiconmyicon92</a>

 Correctly! Goes!

P.S. Please review <a href="designesticons.deviantart.com/.../Large-Business-Icons-278767588">Large Business Icons from designesticons</a>

 I consider, that you are not right. I am assured. Write to me in PM, we will talk.

<a href="www.hpixel.com/.../a>

 I think, that you are not right. Let's discuss it. Write to me in PM, we will talk.

<a href="www.hpixel.com/.../a>

<a href="www.vista-style-icons.com/.../redo_v2.htm"> The message is removed</a>

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?

<a href="www.downloadstock.biz/.../16x16-Pixel-Toolbar-Icons.html"> In it something is. Now all became clear to me, I thank for the information.</a>

<a href="www.tipdownload.com/.../download.html"> Just that is necessary.</a>

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!

!