Best practices for column naming in Sql

Question

Say I have a table called Student. Which of the following naming conventions do you prefer for the columns? You can also suggest your own.

Student
-------
StudentID
StudentName
MentorID

Student
-------
StudentID
Name
MentorID

Student
-------
ID
Name
MentorID

Answer 1

I preffer the second one:

Students
-------
StudentID
Name
MentorID

Where:

• All the foreign keys are identified with ID on the end of columnname.
• The rest of columns are named with easy to understand.
• Also use EndDate, BeginDate for dates.

Answer 2

I would go with the second one.

Student
-------
StudentID
Name
MentorID

I like have the name of the table in the Primary key, but it doesn't need to be on every field. Also MentorID would be how I'd name a foreign key as well (assuming Mentor is the name of the table it's pointing to).

This way the MentorID field in the Student table has the same name as the MentorID field in the Mentor table. Some people don't like it because it can be a bit confusing when joining tables, but I prefer to explicitly name the tables of the fields in joins anyway,

Answer 3

dupe of http://stackoverflow.com/questions/529863/do-you-prefer-verbose-naming-when-it-comes-to-database-columns

Answer 4

Since regular RDBMS are kind of hierarchical, a DBMS contains a database - a database contains a table - a table contains a column - a column contains a value, I don't like the iterative use of table names in the column names.

My vote goes to:

Student
--------
id (pk)
name
mentor (fk) (alt. mentorId)

It's fairly easy to select correct fields, and in case of joins between tables I often rename the column names, i.e:

SELECT s.id AS StudentID, s.name AS StudentName, m.id AS MentorId, m.name AS MentorName
FROM Studens AS s
INNER JOIN Mentors AS m ON m.id=s.mentor

Answer 5

I prefer the last one so that a join between the tables look like:

 SELECT blah blah blah
 FROM Student INNER JOIN Mentor
      ON Student.MentorID = Mentor.ID

But this is nearly as subjective as "do you like camel case?" :P

The main thing is to be consistent. I've had to deal in the past with some databases where they could never decide on a standard. So in some tables the PK would be StudentID, others Student_ID and others ID. Or they weren't used consistently name when used as foreign keys. Oy, I'm starting to rant...

Answer 6

I might use StudentName instead of Name because it will make joins easier. Often I find that I have many, many tables with a "name" and "description" column.

Answer 7

And I'd go with almost the third one:

Student
-------
Id
Name
Mentor_Id

SELECT Student.Name, 
  Student_Mentor.Name
FROM Student
  INNER JOIN Mentor AS Student_Mentor ON Student.Mentor_Id = Student_Mentor.Id

Answer 8

As much as I hate it, I'd go with Option 1:

Student
-------
StudentID
StudentName
MentorID

The reason for this is when joining with other tables with the column "Name", say Course or Degree or something, joining requires that you rename the columns to avoid ambiguous names. Dealing with long names that have table name in it is annoying, but it can save you work on the long run.

Answer 9

since some sql formatters uppercase stuff, i go with the follwing:

student
-------
id
name
mentor_id

that way i can keep word separation in the db.

in OO-code i use the corresponding camel-case names

mentorId, getMentorId()

Answer 10

I usually do number 3

Student-------
ID
Name
MentorID

Answer 11

Stop the madness!

Answer 12

I would personally go with:

Students
--------
student_id
first_name
last_name
mentor_id

I prefer to use underscores because studies have shown that they improve readability of code immensely versus camel-back notation.

I can also understand arguments for just using "id" rather than "student_id", so I'm not averse to that.

Answer 13

I prefer the first one.

By giving the fields a more specific name than just Id or Name, it's easier to see that you are joining correctly, and you don't have to use aliases for the fields if you select fields from more than one table:

select s.StudentId, s.StudentName, m.MentorId, m.MentorName
from Student s
inner join Mentor m on m.MentorId = s.MentorId

vs.

select s.Id as StudentId, s.Name as StudentName, m.Id as MentorId, m.Name as MentorName
from Student s
inner join Mentor m on m.Id = s.MentorId

Also, the word Name is a reserved keyword in some databases (for example SQL Server), so it's not always practical to use as field name.

Answer 14

Name is likely a reserved word, I would never ever use it as a column name. Nor would I ever consider storing names in one field. You really need first_name, Middle_name, last_name, Suffix (for III, Jr, etc.). Consider what you have to do to query a name field when you want all the customers named 'Smith'.

I also would never name an id field ID. I prefer my id fields to have the same name in all the child tables as it makes it much easier to see what you are talking about especially when you have a complex query involing many different ids.

Can only one person ever serve as a mentor? Unikely. Mentor should be a separate table and there should be a joining table with StudentID and mentorID

Answer 15

There is no "right" answer for this. Just pick any naming convention you like (and everybody else who will use your db) and stick with it. There are plenty of well designed naming conventions on internet. Just search Google on "SQL naming conventions".

In my experience people use totally different styles, but it's OK as long as whole application (or all projects in same organization) use the same conventions.

Answer 16

I prefer using this pattern:

student
-------
id
name
mentor_id

_id for foreign keys

Answer 17

I actually like:

Student
-------
Id
Name
IdMentor

I guess it sort of mimics Hungarian notation. There's also more of a visual difference between IdMentor and Mentor.Id, than between MentorId and Mentor.Id.