HOW TO DESIGN AND MODEL DATABASE FOR A PRIVATE HOME LESSON
In this article, we will be discussing the design and modeling of a private home lesson database under the sections below:
Please note: This database design is completely different from conventional school management system. Some of the terminologies do not mean the same as the ones you know in school management system. So I will advise that you please kindly clear your mind and fasten your seatbelt as I drive you through this path.
1) Introduction
2) Business Rules
3) Lucidchart Basics
4) Conceptual Stage
5) Logical Stage
6) Physical Stage
7) Conclusion
Introduction
When creating or designing databases for any projects or organizations, it is important to follow industry approved standards and processes. This process makes it easy as a professional to think structurally. you may also call them database design stages.
While you need to follow those rules for database design best practices, there are no universal rules for developing or stating your business rules. This is simple because most businesses are different in mission and vision. Two businesses may render same service but do not share same value. This design is purely and uniquely based on the mode of operations of Primedclass. Let’s briefly talk about business rules in database design.
Business Rule
A business rule is a statement that imposes some form of constrains on a specific aspect of the database.
It could be in form of traits of a given relationship, some attributes’ constrains or more. Some examples, take a look at the Primedclass database model:
In the students, parents and teachers tables, I enforced the input for gender to “Male” and “Female”. Since it is a very important piece of data we want to collect, I wouldn’t want data like, “M”, “F”, “boy”, “girl”.
In the classes table, the delivery method is constrained to “Online”, “In-person” or “Self Taught”.
Another business rule I will likely enforce is that of address. Instead of having it as an attribute, it might be better to have it as a table. This way, we will be encouraging best practices of making our data atomic and have a good understanding of the region our clients and teachers operate from. I would be glad to get your view on this before implementing it.
A proper implementation of the database rule helps us to easily avoid anomalies in the database, hence maintaining data integrity.
I will walk you through how to design and model a database for a private home lesson business for children from grade 1 to grade 12. Someone may ask, why I chose these age bracket. Well, this is not a hypothetical business. It is a real small business, rendering home lesson services in a city in Nigeria. The vision is to build solid foundation in basic education.
Now you can see why two businesses may be rendering the same service but do not share the same value. This is why your business rules are unique to your business and the value you are selling.
Lucidchart is a simple and easy to use entity relationship web application. You can easily start by creating a free account like the one I used in designing and modeling this database.
Feel free to use any ER web application of your choosing. Login to your account, on your dashboard create new document.
Open the chart interface. To start your design drag and drop any shape you want to use.
As for the ER design, you will need to use the search icon, search for “entity relationship”, then pin to the dashboard.
Next it to drag and drop the type you want. At this point, you can create magic with your power of creativity. I chose to leave my own simple.
Conceptual Stage
First, this is a home service business that delivers in-person or online one on one classes for children from grade 1 to grade 12. So below are the terminologies used in the cause of service delivery.
Classes
This refers to the session spent teaching the student. It is the action performed for which parents pay for. This automatically becomes my fact table.
Attendance
This refers to the record for each class. This records include, the date, time-in and, time-out and the topic taught during the class.
Students
These are the direct receivers of the classes. This is purely a one on one classes for the time being.
Teachers
They are in charge of the classes. A teacher teaches one student at any given time.
Parents
These are the parent(s) of the student. Only one parent can register at time. The children are under the full control of their parents. This means, only parents can register their children.
Feedbacks
At the end of each class, the teacher leaves a feedback of how the class went, this helps relay any challenge the teacher may have faced during the class. The same way, a parent may complain about the completeness or effectiveness of the class.
Bills
This is fully under the parents control since they are the ones paying the bills at the end of the month.
The flow chart above represents the conceptual diagram of the database entity relationship. You may need to take your time to understand it.
The fact table being the Classes has direct relationship with:
teachers who teaches the student,
feedbacks which is given about the class by the teacher and the parents,
students that take the classes taught by the teachers and
Attendance, which is the records taken about the class.
The teachers teaches the student, gives record of the day’s class and drop feedback if necessary.
Parents give feedback about his or her child’s class and pays the bill at the end of the month.
This practically wraps up the conceptual stage of this database design.
Logical Stage
The next stage is the Logical stage. In this stage, we will see the full design and their relationship. We will declare some business rules in line with its vision. Below is the full database design and modeling.
From the entity relationship above, we have a simple one to many relationship for all the linked tables. The business rule are properly stated for an MVP(minimum viable product)
Conclusion
Designing database for any business or industry requires an understanding of how that industry operates. So, as a database administrator or database analyst, you are coming with your technical knowledge, it is important you ask some important questions from the client, stake holders or subject matter experts.
Answers you get from these persons will help your logical stage of the modeling and design. You will get to understand the business rules and values system the business wants to sell.
I will continue the physical stage of this project in another article. I will not create the table manually, I will build an API(Application Programming Interface) with Django or FastAPI. This way, the models I will build will automatically create the tables and enforce the business rules for me.
This design and modeling is by no means exhaustive, I am open to any suggestion that can make it better.
Please go ahead and tell me what you think I could have done better, and if it is in line with the business rules and core values, I promise to implement it in the design and update the view.
Link to the database design and modeling below.
