DEV Community

Discussion on: Database Naming Standards

 
ovid profile image
Ovid

While I've laid out my reasoning and you should feel free to disagree. I point out my id naming reasons, you don't like them, that's fine.

As for table names being plural, as mentioned, many experts disagree strongly on this. Joe Celko, world-famous database expert and one of the authors of the SQL-89 and SQL-92 standards, argues in SQL for Smarties, that table names should be plural because they represent collections, not individual entities.

As for case-insensitivity, section 5.1 of the SQL-92 standard states (where the angle-bracketed words refer to a previously defined grammar):

There is a one-to-one correspondence between the symbols contained in <simple Latin upper case letter> and the symbols contained in <simple Latin lower case letter> such that, for all i, the symbol defined as the i-th alternative for <simple Latin upper case letter> corresponds to the symbol defined as the i-th alternative for <simple Latin lower case letter>.

SQL the language (not the data it manipulates) has been case-insensitive for decades.

Thread Thread
 
aarone4 profile image
Aaron Reese

I'll grant that there is no one perfect answer but I felt it was important to put across a different set of arguments. The most important thing is to be consistent with whatever naming convention you decide to use. The ANSI standard SQL-92 - The clue is in the title. That standard will be 30 years old next year, the world has moved on. Each vendor has proprietary extensions to the standard, my preferred flavour is MSSQL which allows you to define the collation rules for your database which includes whether the database and the data it contains is case sensitive, whether it uses the standard ASCIII character set or allows for 'foreign' characters such as Cyrillic, turkish or accented characters, whether Upper and lower case characters should be treated differently and whether NULL appears at the start or end of the index. This may not be standard, but it is 'real world'.
Please don't quote 'World Renowned' Joe Celko: I have had enough run-ins with him on SQLLServerCentral over the years to know that he is an absolutist and simply cannot accept that data is impure and that applications and their databases are not always (usually) developed by people with Doctorates in data management and who have the full picture of what the end solution will need to look like in 10 years time when the business needs are not well defined and you don't have an infinite amount of time. Joe has some good points and his books are comprehensive but on a personality basis he is way of the mark with understanding how to help people sove the problems they find themselves in. His attitude is - well you shouldn't have gotten there in the first place...

Thread Thread
 
rowlandholden76 profile image
rowlandholden76

I know I am late to the party, but I am going to jump in here. I understand that it is common practice to name the PK, ID. But I have always disagreed with this. Reason being I don't like to spend time researching stuff that should be plainly obvious by reading the code. Here is an example:

WHERE tblUser.ID = tblCar.ID

Obviously, there is a problem here, we are looking at two PK fields, which shouldn't happen except in special cases. But which one is wrong? Unless I coded this, I can't 100% know which side is wrong. The policy may be that the PK is always on the left side, but who is to say that Joe Blow actually did that. We already know he messed up the criteria, so maybe when he messed up he did it all the way wrong and put the actual key he wanted to use on the wrong side. The bottom line is I can't actually know that without doing research on the table designs. This is a complete waste of time. Given that we know the intent, what is supposed to be accomplished here, helps. It may not give us the full picture. and for giggles, lets ay the intent is to see which mechanic is assigned to the car. Now we have a little light, but it is still a mess, Perhaps this is supposed to be the PK tblCar.ID to tblUser.CarID. After all, one mechanic can be assigned to fix many cars in one day. Or maybe, it is supposed to be Pk tblUser.ID to tblCar.UserID, after all, many mechanics can be assigned to fix a car if it is tough job. See my point? These situations are rare, but as programmers shouldn't we prepare of all issues as opposed to ommitting the rare cases?

Another example:

WHERE tblUser.UserID = tblCar.ID

This is clear, this states plainly that the userID field is the PK field. If our policy is to always name Key fields, Pk and Fk respectively, we know the issue is in the design of the tblCar as there is missing a name for the key field. Perhaps this field is a PK, or maybe someone didn't name the FK. The result though, is we know EXACTLY where to find the issue from just reading the code. In this case, we open the design of tblCar and name this field appropriately. If it turns out this field is the wrong field for our criteria, then good for us, we just fixed two issues by finding one.

Thread Thread
 
aarone4 profile image
Aaron Reese

OK. Lets unpack your comment as the arguments are common and as always - Make a decision and stick to it.

Firstly - don't name for tables tblUser and tblCar. table is the default object in the database. Yes User is probably a reserved word so find a better table name (like systemUser or employee). Also when you need to 'replace' the table with a view, for example if the fields get split into two separeate tables, to prevent changing ALL your code you will now need to create the view (or a synonym) called tblUser which is doubly confusing because it now isn't a table.....

A.ID = B.ID is patently wrong in the vast majority of cases. It would only really be valid if you have aliased a field as part of a view or CTE or table B is an extension table to A and is a 1:1 relationship so it is an instant code-smell and that makes it easy for a seasoned developer to spot (so it is a good thinig...) Yes you have to interrogate the code to find the intention but you are doing that anyway.

In you example you suggested that the User table in this context is the mechanic. If this was the case then I would be aliasing the tblUser and (tbl)Mechanic (I wouldn't use the tbl - but each to their own) and then presumably the tblCar should have a field MechanicID or MechanicUserID. Under your proposal the JOIN would look like

tblMechanic.UserID = Car.MechanicID

Under my proposal it would be

tblMechanic.ID = Car.MechanicID Which to me is much clearer and confirms that we are joining a PK to an FK. Your way requires more intimate knowlege of the database to be sure that UserID is the PK on the aliased table.

This becomes especially important if the tblUser is going to be used multiple times in the query (Mechanic, Salesman, Valet, Delivery driver, Service receptionist etc).

You said:

after all, many mechanics can be assigned to fix a car if it is tough job

That is a many to many relationship and therefore should be modelled appropriately in the database with a bridging table (e.g. ServiceTeam)
ID INT IDENTITY(1,1), /*yes I know....*/
Car_ID INT NOT NULL, /*FK to Car*/
Mechanic_ID NOT NULL /*FK to User in the context of the role of Mechanic*/
(alternately as the same person may take on many roles...)
Role_ID /*FK to job role to identify Mechanic, Valet, Driver, Receptionist*/
User_ID /**FK to User table./

Feel free to respond as these discussions help junior developer to avoid pitfalls later on in the project.