Database Design Process
The processes here aren’t the same as the agile model, or iterative approach. They are defined steps to end up having a fully defined database, with it’s constrains, and the structure.
There is no place for many changes, because they are going to cost you a lot. So, you need to be specific, and take things step by step. And, here’s the steps:
1. Requirements Gathering
Understanding what you want to do, and what you have is essential before you can dive into designing a database. We’ll look at the steps in this article.
2. Conceptual Design
We specify the entities, columns, and their relationship. We may use an entity relationship (ER) diagram to visualize the database.
The output is: A conceptual schema (described using a conceptual data model like ER model).
3. Logical Design
It’s concerned about data model mapping; mapping a conceptual schema (like ER model) into logical schema to provide a much detail description.
The output is: A logical schema (described using a logical data model specific to the DBMS like relational model).
4. Physical Design
It describes the details of how data is stored. You start by defining (already modeled) tables, how the data is stored, define relationships, … in DBMS.
This requires dealing with the DBMS, and could involve SQL.
The output is: An internal (physical) schema (described using a physical data model).
A database schema is a description of a database structure, data types, and the constraints on the database.
Requirements Gathering
The first step in whether you are building a mobile application, desktop, or any kind of software is to gather the requirements about what’s actually needed. And, here are the steps:
Take a look at Requirements Engineering in Software Engineering.
1. Why?, and is it feasible?
So, first you study “Why we need a database?”, and if it’s feasible or not. You need to check if it could be implemented under current budget, under the current technical skills of your team, within the defined schedule, And if it does contribute to the whole organization objectives or not.
2. Collecting the requirements
If the answer was Yes!, then you start collecting the requirements. You can do this by having interviews with all the stakeholders; anyone who will use the system.
And try to get any kind of existing data, any kind of spreadsheets, this will help you to in building the database.
And if there is an existing database, then ask, what’s wrong with it? what’s right with it? These questions will help to avoid any of the current problems.
3. Group related requirements
Now, you may need to group related requirements together, resolve any conflicts, or any ambiguity by negotiation with the customers.
4. Specify & Verify the requirements
Finally, you specify the requirements, or in other words you write the requirements in a document, and validate the requirements with the customer to make sure that everything is on the right track.