Designing the perfect database schema is always one of the first tasks in the initial phases of developing a new software project.
Many of the software modules are basically a model of the database and all its tables and fields. This means that a well thought-out database design significantly increases not only the efficiency of the product itself, but also the efficiency of the entire coding process. Therefore, it is essential to invest quite some time in designing a database structure that is perfectly tailored to the use case.
One of the obstacles I personally often face when designing the underlying table structure of a software product is the framework and also the corresponding graphical user interface I intend to implement. Complex database structures without any level of redundancy sometimes require a more complex GUI design or a more complex way of processing the actual entries. Therefore, before designing the database itself, I choose to identify the objects that specifically need to be dynamic and the objects that are more static. This helps me think about possible input GUI design schemes for the desired objects before designing the database.
In this blog post, I will show you the process of creating an efficient database schema using the process I followed to design the database for a software project I will be developing, in simple and understandable steps.
I am planning to develop an app where users answer some questions and then are presented with content based on the answers they choose.
For this purpose I plan to create a web-based content management software with Php, JavaScript etc. based on UserFrosting, a modern user management framework for PHP. This is to dynamically manage the questions, the answers and the corresponding information blocks. What’s also required is for the information blocks to be multilingual.
To display the content managed via the above software, I will develop a Flutter app that will prompt customers to answer the managed questions. The answers are then passed to the server and the corresponding blocks of information are then delivered to the Flutter app via a Json REST api. The app then displays the results back to the customer.
As mentioned earlier, the first step to creating the software is to design the SQL database. Let’s dive in.
1. Identifying the Objects
The first step is to identify all the required objects. To do this, we need to create an abstract model of the project.
In this example, the required objects are as follows:
Questions, Answers, Languages, Block Categories, Blocks and Logics.
Questions: an object that contains a title, subtitle, and other information needed to effectively identify a question. This information includes criteria such as whether at least one answer must be selected or whether it is a multiple-choice question.
Answer: Each individual answer is associated with a question. This object is used to identify an answer.
Language: I plan to offer the product in 3 languages. English, German and French. This object identifies which of the languages is active and which is not.
Block category: each block of information belongs to a block category. The block categories help us to categorize the blocks and show the individual users the information blocks that belong to each category, based on their selected answers. This allows us to have multiple blocks for each category. Based on the answers that each user selects, the block that best matches the user will be displayed. Thus, we can add information blocks that are more common and specific blocks for more specific answers.
Block: The blocks of information.
Logic: This is the heart of the whole project. The plan is to have objects that contain a logical expression indicating which combination of answers leads to which block for each block category.
2. A Draft Sketch
Now it’s time to connect the objects in a meaningful way. In this step, we will connect the objects and determine how the objects are linked together.
Each question has multiple answers, so that a question has a 1-to-many relationship with the answers.
This also applies to the relationship between block category and block. Each block category has multiple blocks. So the relationship is also 1 to many.
On the other hand, each answer and block can belong to a number of logics. These relationships are needed to preprocess the blocks to be displayed to a user who has selected certain answers. The relationship between the answers and the logics and the blocks and the logics is therefore many-to-many. This means that in addition to one table for each object, we need two more tables to create these two many-to-many relationships. The draft sketch for the database looks as follows.
3. The Tables and the Relationships
Now the objects have to be converted into a table and the relationships have to be added to the table structure.
Here you can see the 1-to-many relationship between the questions and answers tables and the block_categories and blocks tables.
You can also see the many-to-many relationships between the logics table and the answers and blocks tables. Creating the logics_to_answers and logics_to_blocks tables is inevitable.
4. The Entity-Relationship Model (ER Model)
The final step in designing a database schema is to create what is known as the entity-relationship diagram, or ERD diagram. In this final step, it is important to define exactly what information each object requires. Although individual columns can be added later if needed, it is better to think about the columns that are critical to the product from the very beginning. In this step, it is also important to determine the type and limits for each column of information. For example, the title of a question must be text and cannot be longer than 300 characters. This is something that must be defined before creating the model.
