How To Design A Simple Database Schema
When you starting out as a Junior Software or Web Developer, you may not be concerned with app or database architecture, you just want to learn how to write code. As you progress, you learn how to write optimized algorithms and adopt programming best practices. But the art of becoming a web developer or software engineer goes beyond knowing how to program.
As Wikipedia defines,
“A web developer is a programmer who specializes in, or is specifically engaged in, the development of World Wide Web applications, or applications that are run over HTTP from a web server to a webbrowser”.
This implies that the primary role of a Web Developer is the development of Web Applications. But what do we mean by Web Application Development?
Web application development is the process and practice of developing web applications. There is a consensus that the processes involved are extensions of standard software engineering processes (Wikipedia).
Wow! This sounds great. That means as a web developer, I can as well call myself a Software Engineer.
I know that developers do not have time to read funny stories that touches the heart of men but for the benefit of beginners or young developer who may not be familiar with the above concepts, it will be helpful for them to understand the connection or relationship between these concepts. If this introductory part sounds boring, you may as well skip the next few paragraphs to the business of the day below. But who is a software engineer?
A software engineer is a person who applies the principles of software engineering to the design, development, maintenance, testing, and evaluation of computer software (Wikipedia).
Simply put, before you can call yourself a Software Engineer, you must be capable of:
- Designing
- Developing
- Maintaining
- Testing and
- Evaluating web applications
Designing entails a lot in web application developments. It covers app architecture, UI/UX, Database Architecture, server architecture design etc. In this article we shall focus on designing a simple database schema to solve a simple problem.
Understanding The Problem
As a web developer, when a client comes to you with a problem, your primary assignment is to understand the problem then find solution to the problem. Your level of understanding can either result in a solution that complicates or simplifies the clients problem. It best that after the client must have stated the problem that you go ahead ask a few questions to better understand the problem before engaging in design and development.
Statement of Problem
Client: “I want to be able to use my system to find my employees, view their details to know when they were employed and the department they are currently assigned to”.
The above sounds like a very simple problem but some followup questions will help to find the simplest solution to it. Its best not to assume but to ask questions for clarification. Questions like:
- Will this app serve employees of only one company or organization?
- Do you have a sample employee form that you use locally?
- Do you have a sample Employee ID card?
- Do you have a strict Employee ID No format?
- Will there be control on who can Add, Delete or View employees?
Assuming the client provides an ID like the one below
The information on this card will go along way to helping you identify required entities and proceed with the design. Let’s break it down in its simplest form.
Identifying Entities
What will you call this app? Most often, clients only describe the problem they want to solve but may not have an idea of the possible solution and the name that will best fit the solution. With regards to the problem defined above, I will simply call the solution Employee Manager. Since we are dealing with Database Schema design, is more sensible to keep your database name in sync with your app name. In adopting some of the web development best practices, I personally favour convention over configuration. Hence, the database name will be employee_manager.
An entity is something that exists as itself, as a subject or as an object, actually or potentially, concretely or abstractly, physically or not (Wikipedia).
When designing database schema, its best to think of database tables as representation of app entities. During app development, if the MVC (Model, View, Controller) pattern is adopted, these entities turn out the be the Models were objects may relate to other objects in some way. The image below explains more
In our employee_manager design, we have identified the following entities
- Employee
- Department
- Designation
We may also define other entities like:
- User
- Role
- BankInfo
- EmployeeProfile
only if we need to add advance features like access control, bank details of employees and other employee details, but let us keep this simple with only three entities.
By convention in web development, database names are preferred as lowercase, table names preferred as lowercase, plurals & underscores to separate two words like EmployeeProfile to employee_profiles.
Considering the convention above, we convert our entities to table names like so:
employee_manager
- employees
- departments
- designations
Once you have done this, next is list table columns and define relationships between two or more entities. Then go ahead and use a tool like draw.io or Microsoft Visio to create a nice design of you database schema like so.
As simple as the above. I made use of the free tool draw.io to design and export the above image. By convention, designation_id and department_id represent foreign keys from designations and departments tables respectively. The rest of the attributes are self explanatory. You can go ahead and add as many more attributes that you wish. But this is the basic principle.
Now, our job is done. If you understand the above principle, you can apply to complex problem solving and design database schema for large applications. For more complex applications, you need a deep understanding of ERDs (Entity Relationship Diagrams). The above is an ERD in its simplest form. Do well to read the following articles:
Best of luck!
You can reach me via my social handles:
- Twitter https://twitter.com/dinyangetoh
- Facebook https://facebook.com/dinyangetoh
- LinkedIn https://linkedin.com/in/dinyangetoh
