The following tips will help you put some good practices into use in your next SQL database design.
- Prepare for the unknown – Never design a database for the ideal situation; the ideal situation will not present itself most of the time. So even if your client says that a customer will not try to book the same seat at the theatre at the same time, it will happen. You have to ensure that the database can cope with unexpected situations.
- What’s in a name? – Ensure that tables and fields have meaningful names. That way, anyone taking a look at the database or trying to debug it will have a much easier time understanding it. For example, CutomerDetails is a fairly straightforward name for a table that implies that it hold details about customers. CustDet, CsDtls, Cdet etc., all require explanations.
- Avoid long names – While keeping the previous tip in mind, avoid having too long a name. For example, OurCustomersCompleteDetails is a little too long and unnecessary.
- Be consistent – Use the same conventions all over your database. Calling a field a ZipCode in one place and them calling PostalCode in another is just plain confusing.
- Analyze first – When you are briefed of the project, don’t rush off and start your coding. Put your thoughts down on paper first. Figure out the data that needs to be stored and have a look at any existing system that is in place to see how that works for the client.