Introduction

Over my whole software engineering career, I have watched and learned the right way and the wrong way to style and format code. Especially when it comes to naming objects. There has been a war raging, for as long as computer science has existed, over the right and the wrong way to name database objects. This is the age old “Plural versus Singular” table naming argument. I am going to document my bias here to just share with whoever wants to read it. It’s not right and it’s not wrong, it’s just how I do it.

Etiquette

I strongly believe that there is an etiquette involved in when it is appropriate to use your bias. When you are new to an established repository it would be very rude to just start using your own style in someone else’s repository.

When in Rome, do as the Romans do.

Saint Augustine

I worked with an ego maniac in the past, he considered himself an architect (wasn’t an earned title). He decided that he would implement his bias on table naming because he felt his bias was right even though it was clear that the naming scheme had already been established in the existing application.

This is just arrogant and unnecessary because like I said above “It’s not right, it’s not wrong…”. All this did was confuse everyone on the project and all due to the ego of one person. In my own brand of protest when he told me to name my tables in plural, which was his bias, I made to sure to name the primary keys in plural also just to be annoying and to assert my bias that plural doesn’t make sense. This brings me to my first point.

Greenfield versus Brownfield

Is the project a greenfield or brownfield project?

• If this is a new project then the team should decide which bias to use. Everyone has to agree with it and stick with it. Don’t be like the guy I used to work with and decide for everyone without asking just because he thought he had that level of authority.
• If it is an existing project, you should continue to follow the already established pattern no matter how much you don’t like it. Chances are trying to change it from one bias to another will be very difficult and needlessly risky to implement. You gain nothing by doing this, so if you plan on changing a naming scheme because your OCD is flaring up; pause – think twice and try to think rationally, not emotionally and without ego.

Brownfield projects

The way to determine which bias to stick with for naming in a brownfield application is to first determine what the bias is. In the case of databases that’s pretty simple, just review the objects in the database. Use what you see is the most common naming convention and stick with it.

If you see that there is an opportunity to change a bias to a different one, then do so with everyone on the same page and with caution. This is often the case when restructuring is taking place or you are migrating away from an old application over to a new one.

My Bias

I will explain my biases and provide reasoning for why I name things the way I name them. I generally dislike it when someone can’t explain why they do something. Often enough the reasons are just because that’s the way they have always done it. I have no problem changing my style, so long as there is a good reason. I don’t accept empty reasoning or being stuck in the past as an answer.

Things no one should be doing

None of what I am listing here should come as a shock to anyone, if you are still doing these things, you are doing it wrong. That’s not even bias, it’s just common knowledge in this day and age.

1. Don’t prefix your tables with “tbl”. Thanks, we know it’s a table, stop doing that it’s annoying. I inevitably always have to hear from someone “Oh, well back in the day you didn’t know which object was which so you had to prefix them.” Cool story bro… don’t care, stop doing it.
2. Don’t prefix every single object in your whole database with your product name or abbreviation. Frankly I have no idea why people do this, it’s redundant. Just don’t.
   1. There are situations where this is necessary, but it only usually occurs when you are programming against a shared product such as Word Press. At that point you need to identify your product.
   2. However, if you have a standalone application – there is no reason to do this.
   3. If you are hosting two applications in the same database – you shouldn’t have done that.
3. Stop prefixing your stored procedures with “usp” or “sp”. There is plenty of material on the internet as to why, not going to repeat it here.
4. Stop prefixing your functions with “ufn” or “fn”. Again, there is plenty of material on the internet as to why, not going to repeat it here.
5. Don’t store custom made objects in the master database! That’s just asking for trouble and it’s not obvious to ANYONE that you are doing it. Just stop! Plain and simple, if it is for your application it belongs in your application’s database or somewhere else, but not in the master database.
6. Don’t include the name database in your database name. We know it’s a database. Stop being redundant.

Things you should be doing

Include schema names in queries

I have come across numerous instances where people don’t include their schema name in their queries because they don’t understand why they should. Well I am here to explain it to you:

1. Schemas are specific to the user logging on. Your user can default to a schema other than “dbo”.
2. The schema “dbo” is the default, but it doesn’t mean that it is the only schema in your database and to make that assumption is wrong.
3. You could be executing or querying against the wrong object without even knowing it – then we get the “Works on my machine” scenario because someone was too lazy to type some letters in front of their table names.

Practical example

The only difference between which of these two tables will be executed is the current’s user’s default schema:

-- If you execute this first line, which table is going to be truncated?

TRUNCATE TABLE Invoices

-- dbo schema? -> dbo.Invoices
-- OR
-- archive schema? -> archive.Invoices

Now ask yourself, “Is it worth the risk of being wrong?” – do yourself a favor and just type the few letters in front of your table names. It won’t kill you to do it.

Name your constraints

It’s really easy to just not name your constraints as a developer; but when you are the one handling production databases and the deployments to those databases and you encounter unnamed constraints during a deployment it causes a lot of unnecessary heart ache. It’s called empathy, learn it.

Unnamed constraint example

Go to a SQL Server and create this table on two databases:

CREATE TABLE dbo.SomeTableThatDoesNotExist
(
    [SomeTableThatDoesNotExistId] INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [DoesNotMatter] INT NOT NULL
)

You will find that you get something like this for your primary key constraint name:

When you run it a second time on a different database or even drop the table and create it again on the same database you will see your primary key’s constraint name has changed again:

Now imagine you have to change this primary key constraint name on let’s say 50 databases for argument’s sake. How do you do this if you never know what the name of the primary key is going to be? Now for the same two constraints that you just created do this exercise without the help of a UI:

Drop statement exercise

1. Write a single static drop statement for the Primary key constraint of that table.
2. Execute the drop statement
   1. You cannot modify it between databases
3. NO, you cannot use dynamic SQL for this exercise

After you do that – I want you to imagine how that makes someone doing a production deployment feels, when they have to do it for as many databases as they have to maintain spur of the moment.

Exception to the rule

There is only one situation I have found so far where it is impossible to name the constraints even if you want to and I blame Microsoft for this because I can’t understand why. You cannot name unique constraints on a “User-Defined Table Type“. You just can’t…

Foreign Keys

You are forced to name foreign keys. It is impossible to not name them when creating a foreign key. Not sure why Microsoft didn’t just randomize the name for this with a partial GUID like everything else.

How to name your SQL objects according to my bias

• Table names are to be named in the singular
  • My bias is that each row is the representation of an object. Therefore, your table even though it holds multiple of these objects, the singular is what is used to represent one of those objects.
  • Additionally, I find it redundant to state the obvious. Yes, a table holds multiple rows, that’s very clear already you don’t need to state it.
• Don’t use abbreviations unnecessarily – writing out the whole word won’t kill you. There are cases where an abbreviation makes sense, but most of the time it doesn’t and just causes confusion and misspellings.
  • When I see things like “Tm” instead of “Time” I really wonder what did the author think they were saving? Why? Just write out the whole name and be CLEAR.
• All official concrete objects should use PascalCase
• Parameters and Variables should use camelCase
• Prefix local variables as you would normal C# objects there is nothing wrong with doing that. The only time I have heard someone complain about this is because it bothered their bias standards. No reasoning, just arguments with no substance.

Basic elements

These are the basic elements of any table. I am still in the process of investigating what is the best way to handle time zones which is why right now I favor UTC time. I need to do a deeper dive into it as I know there is a datetimeoffset data type which I still haven’t had the pleasure of working with.

Constraint naming

I get miffed about people not naming their constraints as I have shown above, but also because I don’t find there to be an excuse. It’s simple, it follows a very basic formula as shown here:

Table detail

As you have probably gathered from the way this whole post has been written, repeating yourself is not something I hold in high regard. I feel this same way about someone using the table name all over the inside of the table. I think it’s pointless and redundant.

Bad example

It hurt me to write this example, but what hurts more is knowing I have seen garbage like this before and it drives me crazy.

CREATE TABLE dbo.Employee
(
    [Id] INT IDENTITY(1,1) NOT NULL,
    [EmployeeFName] VARCHAR(50) NOT NULL,
    [EmployeeLName] VARCHAR(50) NOT NULL,
    [EmployeeAge] INT NOT NULL,
    [EmployeeStartDtm] SMALLDATETIME NOT NULL,
    [EmployeeEndDtm] SMALLDATETIME NULL,
    CONSTRAINT [PK_EmployeeId] PRIMARY KEY (Id)
)

1. The “Id” column is not prefixed with the table name
2. Every column except the “Id” column is prefixed with the table name for no decent reason I can think of other than to be wordy any annoying.
3. It was too difficuilt to spell the words “First” and “Last”, mass amounts of time were saved by just using “F” and “L” respectively. They needed time to write the table name as a prefix after all…
4. SMALLDATETIME is being used instead of its replacement DATETIME2(0)
5. Using “Dtm” to maybe represent DateTime when really all we are going to store is the date here so why not just call it “Date”?
6. Finally, a primary key that is named, but has very little detail as to where it comes from. Thanks. Well at least it’s named I guess…

Refactored with my bias

CREATE TABLE dbo.Employee
(
    [EmployeeId] INT IDENTITY(1,1) NOT NULL,
    [FirstName] VARCHAR(50) NOT NULL,
    [LastName] VARCHAR(50) NOT NULL,
    [Age] INT NOT NULL,
    [StartDate] DATETIME2(0) NOT NULL,
    [EndDate] DATETIME2(0) NULL,
    CONSTRAINT [PK_dbo.Employee_EmployeeId] PRIMARY KEY (EmployeeId)
)

Refactored to be explicit but succinct.

Reserved keywords

Some people shy away from using reserved key words as part of their table’s column names. I have yet to find a reason to not do this other than I have to wrap those column names with square brackets which isn’t a deterrent to me. Until I can find a reason to stop doing this, I will continue doing it and no one can tell me otherwise.

Examples

• Key
• Value
• Values
• Name
• Description

Again – been using these names without consequence for years now in production. I have had NO problems. I don’t subscribe to mysticism as a caution so either give me a good reason or I will continue as I have.

Programmatic objects

This is probably one of the hardest areas to name things because often names can get too long when following convention. This can’t be helped sometimes, again it’s not going to kill anyone if the name is too long, it’s just not fun to deal with. I would rather have a long explicit name than a short mysterious name.

CRUD operations

Pick a convention and stick with it, they all mean the same thing in the end. Just be consistent!

This goes back to the beginning of the post, none of these are wrong, just use one and stick with it – don’t change half way through and don’t be a jerk and just start using one over the other because you like it better.

Concept of ownership

I have a very simple model I follow, it’s a lot like how REST uses controllers to bundle together a bunch of routes:

{Base object name} + “_” + {Action}{Description}

The idea here is that these actions belong to an object. Obviously this doesn’t always work out, but it works out more often than not.

Stored procedure examples

These are examples of stored procedure names. This does get a bit wordy, but it keeps the objects grouped together and it is clear who you are executing on.

Function example

When it comes to functions, I would say that most of the time they should be generic enough to not be owned by any one object. I like using functions for well-known calculations. Name the function according to what it is doing – plain and simple.

CREATE FUNCTION [dbo].[SafeDivide]
(
    @numerator decimal(18, 2),
    @denominator decimal(18, 2)
)
RETURNS decimal(18, 2)
AS
BEGIN
    IF @denominator = 0 RETURN 0
    
    DECLARE @value decimal(18, 2) = @numerator / @denominator;

    RETURN @value;
END

Scrutinizing my own bias

• I have always used “UK” for my unique constraint prefix. I don’t have a reason other than that’s how it was taught to me and it stuck. I am flexible though. I have seen these other variations:
  • UQ – I think this is closer to the word “Unique”
  • AK – I don’t get this one but I have seen it used
• I put the whole table name into the constraint for a few very simple reasons:
  • Constraint names have to be unique for the whole database, so make the name unique to the table
  • This is why I include the schema name and the table name so that it is very explicit what the constraint name is for
  • It gets pretty long for the foreign keys, but it doesn’t matter, it’s not going to break anything and it won’t kill you to do it either.
• I have received criticism for the way I name my constraints because I am forced to use square brackets to encapsulate the names. I have been told it is bad practice to put periods in the constraint names. These are all unfounded claims and I have been doing it for years with production databases with thousands of users. I have had no problems doing it. Like I have said before – give me a good reason and I will change it. Until then, it’s just bias.
• Sometimes my object names do get too long, but naming is very difficult. You need to find that balance between meaningful but succinct. It’s harder to do than it sounds. Especially when you know that you will be stuck with this object name once it starts being used. You have to choose carefully.
• I am not infallible, but I do have a lot of experience and I have been honing my skills for a long time.