A good database design is the essential foundation for every application. But still, from time to time we have to (re)build an application on an existing database and we feel the need to scream. The original title I had in mind for this post therefor was "How to annoy a Developer". But let's stay positive. No one does that on purpose. Often people simply don't realize why some things are important later on in the development process. So here are some tips and tricks to design a database that your developers will love.
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.
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.
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.
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 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.
Feel free to leave a comment or send a question to firstname.lastname@example.org. Let me know if you got inspired and may the source be with you!