If you maintain an enterprise database, you may have made some mistakes while planning and designing the database. If you are a DBMS professional, then you may have come across many such errors, which had ultimately collapsed the database. Maybe some of us are still doing the same without knowing that we are making a mistake.
It is impossible to go back in time to undo the errors, but it is possible that you can save yourself from future chaos by avoiding making a mistake at this point. Going through this article may help you to save many hours spent on fixing the code and design problems at any point. We have split these into two groups as technical and non-technical, whereas both these are important to consider in database design.
If you don’t know anything technically, you may not know how you may do something in the technical aspects. However, it is not difficult for you too to understand the technical errors in this list. In the case of non-technical skills, people usually commit mistakes by being ignorant or forgetting about them. However, these skills are important in the case of database design to enjoy an optimum performance. Knowing these errors will add value to your code and design and enable you to solve real-world problems.
In this article, we focus on the non-technical database design mistakes, which applies to most people and looking a the first point even without technical knowledge.
Nontechnical database design mistakes
Mistake #1: No planning
This is the gravest non-technical error to commit, but unfortunately, this seems to be very common. We get excited as a project start, and by going into it, we may think that everything about it is great. In the beginning, a project remains a blank page, and the makes are happy to be with something which will offer a better future. A great future is a final result we aim at. However, most of the time, we are left unfocused. This is the part where we tend to make some critical mistakes.
Before sitting down to draw the data model, one should first be sure that you are completely aware of the business model and what you want to achieve with this database project in the future. The one who designs the DB should understand the business’s workflow and should be able to make suggestions to improve it if needed.
During database planning, there are few questions you need to find answers for as:
- Which tables may be the central tables in the data model?
- You may have a few of them being the central tables, while other tables may be usual tables. Also, consider the relations and dictionaries between tables.
- What are the names to be given to the tables in your data model?
- Keep the terminology the same as your business terminology and workflow.
- How long will the project take?
- This is important to understand proper planning and execution.
While you are ready with these answers, you can start to devise an initial solution to the problem at hand. This first draft need not have to be a complete application, but smart documentation with the outline. For database planning, you may also take the assistance of remote DBA services providers like RemoteDBA.com.
Mistake #2: Insufficient communication
While you start with the database design process, you may probably start to understand the major requirements. Some of these are general for all types of businesses, like user status and roles. Some others, like the table structure in your data model, maybe quite specific for the business. Say, for example, if you are building a DB for a retail store, the tables may contain products, pricing, etc. For a cab rental service, it may be vehicles, clients, drivers, etc.
Everything may not be obvious at the start of your project, so that you may misunderstand some of the requirements. The beneficiaries may also try to add some functions, which you may have done differently. So, the process may keep on changing. The changes usually required are adding tables, but sometimes you may be simply removing or modifying the tables. If you have already started writing the code to use these tables, then you may have to rewrite it also in between if needed. In this case, to reduce the time spent on the work and counter any unexpected changes in it, one should:
- Talk with developers and business clients to ask some vital questions? Once on becoming confident about the model, start with the coding.
- If you face any major changes in your design and a lot of code already written, you may not try for an instant fix. Do the same way as it could have been done irrespective of the current scenario. A quick fix may save time and work fine for a while, but it can ultimately turn out to be a nightmare.
- If you find something is okay to go with now but maybe an issue at some point, don’t leave it that way. Analyze that part closely and implement the needed changes to improve the system’s overall quality and performance. It may cost some time but can ultimately deliver a better product, which can perform much better.
If you are trying to make some changes in the data model while you see any potential problems or opting for a quick fix instead of promptly doing it, you will have to pay for it sooner or later.
Mistake #3 – Poor or no documentation
For many out there, documentation comes only after the end of a project. However, if we are well-organized, we may document things through the project itself, and in the end, we have to wrap everything up well. However, this is not usually the case. Writing of documentation happens only before the project’s closure, which tends to miss out on many things.
The cost of a poorly document project can be quite high. Suppose if you find a bug a few months later after completion of the project, without a proper document to refer to, you may not know where to start and how to go about it. So, as you are working through the project, don’t forget to write comments. Add explanations to everything needed and write down all these as a detailed note, which will surely be used one day.
Along with the above non-technical mistakes to avoid, you should also abstain from the practice of not using a proper naming convention, using the Entity-Attribute-Value (EAV) model, usage of GUID/UUID as primary key, insufficient Indexing, redundant data, etc.