Be Clear

Naming is the most crucial thing. Nobody wants to go through the documentation and the database itself should be self-explanatory. Do you need to store customers in your database? Then call that table Customers. Is there an ID in that table? Call it CustomerID.

Avoid using abbreviations. They often are hard to figure out. Don't worry that a developer will have to type the full word. They have intellisense (auto-complete) to do the hard work there.

Be Brief

Don't call the Customers table tblCustomers. The tbl-prefix is redundant because we already know that it is a table. Same goes for the column name strAddress. Really, we know the difference between a string and a number. Be clear but also be brief.

Think ahead

A customer is a company you are selling things to. Are you going to store other types of companies, like suppliers, in your database as well? Evaluate for your situation if a table Companies with a column CompanyType would be a better idea. The less tables you have, the easier it is to find the correct tables and columns.

Be Consistent

If you create a table Companies, then use the column name CompanyID. Not CustomerID. Same in your SalesOrders table where you want to refer to the Companies table, call it CompanyID. Yes, you could argue CustomerID would be possible since it does not make sense to have a SalesOrder for a supplier from Companies. But that is something you would have to deal with higher up in the Business Logic. In the database it is all about consistency first.

Same goes for column names. Believe me, it is really annoying if you use Email in one table, EmailAddress in another one and E-Mail in yet another one. And please, don't go for E Mail, but more on that later.

Be Consistent, part II

Give a hint on the data-type. Is the primary key of your Companies table a unique identifier? Call it CompanyID. Do you prefer to use auto-numbering? Call it CompanyNr. Is it alpha-numeric? Call it CompanyCode. Also, if you would choose a unique identifier for your Companies table, use this for all tables. Did I mention it is all about consistency first?

No spaces please

Ah yes, back to the E Mail column. Don't use spaces in table names, column names or any other name. Yes, it is possible but developers will hate you. Why? Because spaces don't work in code and we have to put brackets around it, like [E Mail], [First Name] or [Visit Address]. It really messes up our typing and it slows us down.

Does your column name has multiple words, like Visit Address? Then use Pascal Case (VisitAddress) or Camel Case (visitAddress).

Avoid using SQL keywords

It is not a great idea to call a table Table. Nor is it to have a column name Select. Some words are reserved keywords and they are "in use" by the database itself. Some of these could be used but in most cases we would (again) have to put brackets around the word, like [Table] or [Select]. If possible, avoid the words from the reserved keywords.

Documentation in the database

Ok, so you have a Companies table with a CompanyType C for Customer, S for Supplier, R for Reseller and so on. Put a table CompanyTypes in the database as well with the different options. Remember that the documentation isn't always supplied to us. So if it is there in the database, you can be sure we will find it. And we will love you for that.

Be brave: correct fast

So you renamed your Customers table to Companies. But it still has the CustomerID column? Change it. Change it now. Did you use spaces in all your column names? Change it now while you can. Once a developer starts building the application on your database, it will be very time-consuming to change it in the code as well. The more is built, the harder it gets.
And one day you will have hit the point of no return and developers will be annoyed by your mistakes for many years after that.

That's it. It is that easy

That's really that is all there is to it. With consistency being the most important thing to remember. If your naming is consistent and self-explanatory, we will forgive you for using a reserved keyword. After all, we don't know them all ourselves. But consistency should always be on top of mind when you design your database.

And remember that the database is the foundation for your application. You can do it right only once. Take your time and be critical of yourself. Let it rest for a few hours or days and then go back. See if you still like it.

You can leave a comment or send a question to andre@andrespeek.com. Let me know if you got inspired and stay safe and healthy always!