How to create your own database
Introduction
This article contains useful information for those who want to create their own database but do not know where to start.
I have been working with data for 20+ years and been involved in lots of projects related to the creation of databases and apps for collecting and processing large arrays of data. I have tried many data tools over this period and eventually got an insight into tools and ways to solve different types of problems. I hope my experience will be useful to you.
Why create your own database
If you are reading this article, you must have faced the task of creating your own database. Below are the main reasons why people create them.
1. Too much data around
This is probably one of the main reasons. Whatever you do, you accumulate information, which needs to be properly arranged and stored. Information can be stored in paper documents, emails, forms, and spreadsheets. At first, paper documents can fit on your desk shelf, while electronic documents can fit in one folder on your hard drive, but you will notice pretty soon that you are overwhelmed with the accumulated information.
2. It becomes difficult to find the information you need
I am pretty sure that each of you have been into a situation when you urgently needed certain information, but you just couldn’t find it. You were trying to remember where the information was stored, searching through emails, various instant messengers, and folders with documents on your desk. If you estimate how much time you spend on these searches every year, you would be horrified.
Aside from wasting time, these searches constantly interfere with your important tasks.
3. Need to share information or co-work with it
Suppose you have an excellent memory, and you are a very organized person. You know exactly where every piece of information is stored and are able to find it in a few minutes. However, problems begin as soon as a need arises to share your information and co-work with it.
Other people often either do not want or are not able to understand how you arrange the storage of information and what rules apply. Once you start sharing your information, you come to the conclusion after a while that you lose control over your data and cannot trust it as before.
Steps to create your own database
The whole process of creating your own database can be divided into 4 steps.
1. Analysis and development of database requirements
You need to analyze the data that you are going to store in the database at this step, as well as decide on their composition and volume. You should decide whether your database is multi-user, and if so, how many users there will be. You also need to make a list of all data management functions. The better you work out the requirements at this step, the less the risk of making mistakes at the next steps.
2. Selecting tools for creating your own database
You need to select one of the available tools for creating your own database, based on your database requirements. The choice is wide. There is also a lot of information about the tools online. You can try most of them for free and see if they suit you. You may also need the help of professional consultants at this step.
3. Create the database structure
Once you have selected the tool, you need to create a base structure in it. This is a very important step, because the wrong structure will prevent you from arranging the proper search and can make it difficult to scale and refine the database. It must be understood that making changes to its structure will become more and more difficult as your database fills up with data.
4. Filling the database
Once the structure is created, you can start loading data into the database. It may turn out that the database structure is not optimal, when you upload real data at this step. In this case, you will need to modify or even completely redesign it. It may also turn out that the selected tool to create the database is not suitable for solving your problems. Therefore, it is recommended to start uploading the data in small portions and make sure that everything works as expected in order to avoid problems later.
Tools used to create your own database
There are four main types of tools used to create your own database:
Let’s check out the scope, the pros and cons of these tools.
Spreadsheet applications
Almost everyone uses spreadsheet applications today. When we need to collect some data and compile it in some structure, spreadsheets are the first thing that comes to mind.
It is a very powerful tool for data processing, and many people use it to create their own database.
A modern spreadsheet application like MS Excel has a user-friendly interface, plenty of functions, a feature of creating simple interfaces, and a specialized programming language.
I have seen lots of business process automation systems built on top of Excel and VBA that used a wide range of features those products provided. However, most of them eventually had to be replaced by tailored solutions due to the limitations of spreadsheet applications.
It is also worth noting that online spreadsheet applications like Google Sheets, Smartsheet, etc. have become more popular recently. Their features are still inferior to classic spreadsheet applications, but they already excel those in terms of convenience and accessibility in many ways.
Limitations
- It is very difficult to create a rigid data structure in spreadsheet applications. There are four main structural elements in spreadsheets: sheets, rows, columns, and cells. When you start creating a document, you endow these elements with a certain meaning and thereby set the data structure, relationships, and limitations. However, it is created in your mind only. The software is not aware that the cells of one row are interconnected and describe a specific object, other cells contain data, while the third category of cells contains column names.
Such variability and flexibility are good when you deal with a small amount of data that feature a simple structure and you do not co-work on those data.
- Spreadsheet applications are not designed to process large amounts of data. There is usually no problem with a few thousand entries, but as the number of entries grows, such a spreadsheet becomes difficult to deal with. Besides, the risk of accidental data corruption or deletion is greatly increased. Sort them unsuccessfully once, and you can completely lose the data integrity.
- Data are difficult to link. If you have several spreadsheets, and the data in them are logically connected, you need to arrange them in the application as well. This is possible but requires certain efforts and strict control over the contents of all cells and sheets contained in the links. You can easily lose important information – all it takes is making a few wrong changes.
- Co-working. Spreadsheets were initially intended primarily for single-user use. Now modern online applications allow you to co-work. However, if you grant access to others and allow them to modify the data, you may eventually find out that it was corrupted or deleted — again, this may happen because other people misunderstood its structure and how the data were related.
File storage
If you need to store files and their description in a structured way with the purpose of quickly finding the desired file, you can create a file database as a structured file storage. It can be done on your hard drive, corporate file server, or cloud file storage.
Almost everyone already has a database of such a kind on a PC or laptop. You structure your business documents, pictures, videos, and other files by arranging them into appropriately named folders.
In this case, the information storage is structured using a hierarchy of folders and standardized file names, such as document type, document number, document date or shooting date, shooting location.
The ease of implementation and clarity are the advantage of this solution, but file storage also has some limitations.
Limitations
- Search. As a rule, you can search only by the file name or phrases it contains. This entails two problems.
The first problem is the limitation on the length of filenames in the file system. You just won’t be able to create long file descriptions.
The second problem is that you can only conduct very simple search for files by phrases found in their names. You won’t be able to make complex queries with multiple search parameters. In case of personal use and a small number of files you can do this by simply viewing the contents of folders, but this will be impossible in corporate databases.
- Accessibility. If you use files within your organization only, there shouldn’t be any accessibility issues. However, if you need to access information online — for example, from home — or provide access to your information to a third party, this will be very difficult in the case of intra-network storage. Using cloud storage solves these problems.
- Sharing access and setting permissions. Uploading all files to a file server and opening access to everyone is risky and not quite correct. You must administer access to information both at the level of folders and actions available to users. This is where IT specialists must be involved. Besides, it is also difficult to control changes in the structure and files.
- Backup. Please do not forget to back up information, as hard drives on your computer or server are not immortal, unfortunately. This task falls entirely on you or your IT specialists.
Classic DBMS
Classic DBMS are the most powerful and flexible solutions to create your own database. They allow to create absolutely any database of any scale and with any interface. Almost all corporate systems are based on some DBMS — usually on Microsoft SQL Server or Oracle. Most online platforms and websites use the MySQL DBMS.
Classic DBMS have almost no limitations but a huge number of administration, integration, and management tools. However, you should understand that no perfect solutions exist, and this approach also has its drawbacks.
Limitations
- Interface. Unfortunately, users of your database will not be able to work directly with the DBMS, as this requires the development of a special interface. The development of this interface is the most time-consuming part of the solution. You will need to outsource this task to a software developer or a firm specializing in application development. Before finding developers, you will need to set the terms of reference for development first, and then constantly monitor the development process and test the resulting interface. These tasks are time-consuming and require relevant knowledge.
- Maintenance and development. You should understand that the interface created for your database will be custom, unique and tailored for you. Of course, bugs will emerge, the interface will need to be finalized, and significant resources will be required for its maintenance. Difficulties may also arise when you want to significantly change your product. As a rule, developers refer to your terms of reference when creating the interface and structure of the database, so they do not include great opportunities for its further development in the system. Therefore, when you want to upgrade your database, it may appear impossible due to significant limitations of the chosen platform or inherent in the code created by the developers. Sometimes you will have to develop a completely new solution because of these limitations.
- Time. It will take time to develop the interface. And it often happens so that it takes much more time than originally estimated.
- Risks. What if your interface developer refuses to support your solution or the technology it is built on after a while? It is impossible to seamlessly change the developer, so you face a kind of addiction.
- Price. This is a significant drawback, because developing an application from scratch costs a lot of money. Besides, the cost of maintaining the resulting solution will add on top of that. While the costs of developing and maintaining typical replicated and cloud solutions are evenly distributed among all the customers of the developer of such solutions, in your case all the costs of maintaining your unique solution will fall entirely on you.
Online database design tools
Online database design tools have appeared quite recently and became possible due to the emergence of the modern and inexpensive cloud infrastructure (amazon aws, google cloud, ms azure) and the development of technologies designed for creating multifunctional web interfaces.
The modern online database design tools strive to combine the power of enterprise DBMS with the ease of use of the modern web applications.
Their main goal is to enable the creation of full-fledged online databases and web applications with minimal costs for their creation and maintenance. Unlike classical DBMS, design tools normally contain both the database itself and features for creating an interface for it and adapting it to solve specific user problems.
At the same time, the design tools contain the features for efficient collaboration with data, including access control and monitoring tools.
You will not require programming skills or their development to create a database and its interface. Almost anyone with average computer knowledge is able to create a database application in a few hours today that is in no way inferior to enterprise-class solutions.
There are following key advantages of database design tools:
- accessibility,
- ease of setup and administration,
- speed of creating databases, and
- low maintenance costs.
Of course, there are some challenges as well.
Limitations
- Functions. Although the list of functions of these solutions widens every year, they are still limited. For example, you won’t be able to add your own button, change the color of elements, or add a custom event handler. You will have to adjust your processes to the existing functions.
- Online only. In order to reduce the cost of the solution and add functions as quickly as possible, developers of database design tools have to avoid installing the system on client servers. As a result, it arises concerns about the security of the customer data or loss of control over them. As the market for cloud solutions develops, the reliability and security of data storage in the cloud increases.
By experience, the risk of loss or unauthorized access to data stored on our customers’ servers is often greater than the risk of losing the cloud data they store with us. This is because the level of IT systems administration in most companies does not correspond to the current level of threats.
How to choose a solution
It must be said right away that there is no universal algorithm for choosing a certain solution. It is recommended to try different options or switch from one to another as needed. The choice of solution depends on the following:
- volume of the created database,
- number of users,
- complexity of the database structure,
- composition of data management functions,
- allocated budget, and
- required implementation period.
The recommendations are the following:
- If you are going to store a small amount of structured data mostly for personal use, then a spreadsheet application would be the best option.
- If you need to store files only and share them with a few people who understand and follow the structure and rules for naming files and folders, then it is better to opt for file storage.
- If you need a specialized database and a custom interface with lots of unique features closely linked to your business processes and other systems in use, it is better to choose a classic database and custom interface development.
- If you need an understandable and simple multi-user database on the one hand and the one that has the basic functions and capabilities of classic databases on the other hand, then you should opt for online database design tools.
The design tools will also be a good option if you have not decided on the database design tool. If you start it with the design tool, you can easily migrate to another solution later.
What you need to pay attention to when creating your own database
We would like to highlight a few important points that you should remember when creating a database.
Wrong requirements
All the failures in database creation projects in companies usually happen due to incorrect requirements for the future database: the data structure and interface functions. This is understandable – their proper formulation requires experience in creating databases and deep knowledge of in-house processes. Unfortunately, if you hire third-party developers, they may have experience in creating databases but lack expertise in your company. Vice versa, you can thoroughly know the structure of all processes in the organization but lack understanding the intricacies of databases.
Besides, no matter how well the database is designed, its actual operation sometimes requires significant adjustments.
Therefore, the recommendation is the following. Try to move from simple to complex: create a prototype of your database in Excel or better in an online database design tool. Try working with this database and co-working. Try using the available functions to solve your problems. You will be able to form more precise requirements as a result. In any case, the data you use in the database will not be lost — you can always export it and use in the final product.
It may turn out that inexpensive database design tools are ready to cover 80-90% of your requirements and the remaining 10-20% are not worth the money and effort that is required to implement them. It is also worth keeping in mind that everything is changing so quickly that by the time you implement 100% of the functions you need, the task may already have radically changed.
Poor usability
A common mistake in developing requirements for a database and creating an interface is ignoring issues related to the interface usability.
In particular, the efficiency of using the created database largely depends on the proper usability.
A poorly designed interface entails additional time wasted on training employees and working with the database.
It must also be taken into account that sometimes the most time and money is spent not on buying or developing a database, but on entering information into it. The cost of filling the database can many times exceed the cost of creating its structure and interface.
There may be problems with the interface user friendliness and the performance of data entry.
The less time you spend on entering data, searching and modifying it, the better effect you will get from using your database.
Unfortunately, often there are databases with a complex, incomprehensible structure and a terribly confusing interface for entering information. For instance, entering the same data can take dozens of seconds in a properly designed interface and minutes in an interface where usability was neglected.
When spending extra time on entering data, you waste it, which means you lose your money.
Conclusion
We hope this article will help you to create your own database. If you select the tool properly and avoid major mistakes, you will get an efficient solution to your problems.