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
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,
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
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()
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.
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...
I preffer the second one:
Students
-------
StudentID
Name
MentorID
Where:
ID on the end of columnname.EndDate, BeginDate for dates.
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.
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.
I would go with Number 1:
Avoid reserve names like "name". give fields distinctive names. I you have to repeat the table name, so be it, although I do not like seeing the table name in all the fields.
Avoid just using ID as then you have NO idea what ID is to what table. Make it unambigous ANd you then have to qualify it anyways. student_ID = mentor_ID is a WHOLE lot more readable than a.id = b.id. That is not useful, hard to read, have to then figure out what a and b is and is NOT an agile practice. Code/SQL should be easy readable w/o commenting.
User of underscore helps with readability, camel case aside (as that is what I use in C#) I always put the PK as the first field name and the associated FK as the 2nd, 3rd, etc fields.
Do not end a field name with _s or _d to deliniate string or date.
I like things tidy and unambigous because i want to be considerate to others coming behind me that have to do maint on the DB. Too many people drag bad habits from Access into SQL. Mostly because they had no mentor to help them learn! :-)
Remember, on going maintenance is always a larger on going task than original development.
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.
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
As a side note, like the shortening of words like Company, Brother and Number to Co, Bro and No, I would recommend Identity be shortened to 'Id' instead of 'ID', as the capitalisation of the letter 'd' suggests that 'Id' is an acronym instead of an abbreviation.
Student
-------
Id
Name
Mentor_Id
inner join Mentor m on m.Id = s.Mentor_Id
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
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.
I prefer using this pattern:
student
-------
id
name
mentor_id
_id for foreign keys
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.
Student
-------
Id
Name
MentorId
This would work for me.
