Relational database design [part 1]

Tram Ho

Preamble

In recent years, the amount of data generated by machines and humans has increased dramatically. You can hear a bunch of big data keywords in the mass media. There have been many data management systems with the main job of collecting, storing data, recovering data and converting them into information. The faster the information is obtained from the data and the closer it is to reality, the more accurate and reliable the system will be.

To be able to build a good system, the right database design is extremely important. In this article, I want to share with you the simplest relational database design method to help build a database system based on the system’s requirements that can minimize data redundancy and reduce data redundancy. Easy to restore and maintain.

Overview of relational database design

Important Concepts

  • Data (Data) discrete data or according to a collection of things, events around human life such as data, words, images, sounds, characters … to measure, observe or simply a description of something. Depending on the circumstances, the data has different meanings and they are aggregated and stored on electronic devices, mainly computers.
  • Database: A computer database is an organized collection of data files, records, and fields.
  • Model (model) is an abstraction to represent the real world in a certain aspect, such as a chart, table, etc. to help users easily understand the content that the model wants to convey. There are two main levels of modeling:
    • Logical level: Ignore the implementation factors, system settings, only focus on describing the nature and operational purposes of the system.
    • Physical level: Focus on all elements to deploy, implement the system according to the clarified architecture.
  • A database model is a blueprint for a data warehouse that models how data should be stored. The data in the database model is represented in a clearly structured way in the form of concepts, to represent the relationships of data objects, the semantics and constraints of each object.

Advantages and disadvantages of database design

Previously, people only managed data with separate files, this method is only suitable in terms of data management at a small scale, but for large data management fields, it requires the use of data. use to the database.

Managing data using a database also has its own benefits and limitations.

About advantages:

  • Managing data with a database brings positive benefits, making storing, retrieving, and using data more efficient.
  • Information is managed in a single place, easy to manage and ensure data security, avoiding information leakage. Besides, the data is organized in a systematic way, making it easier to share the database.
  • When the whole company shares a database, other departments can easily access the database and get information when they need it, which helps to avoid data duplication and waste of resources. When the database is shared, the information is updated on the database, when users get the data, the data will be completely synchronized.
  • Finding information is also easy when the data is managed by the DBMS, we can add filters, search, and group information together by using queries.

About the cons:

  • When something goes wrong, sharing a database can cause all data to be completely lost. However, this drawback can be easily overcome by backing up data regularly.
  • Sharing the same database when there are too many queries will cause bottlenecks and system crashes. However, in practice, the amount of queries to the database has to be estimated and invested in the server configuration accordingly.

Database model analysis and design

  • At the beginning of a project, the design team gathers requirements from customers, stakeholders, and those directly involved in project development. The collection of information can be conducted through face-to-face interviews, the use of questionnaires, observation of operations, processes, reports and documents. Based on that, the design team will analyze the requirements to capture in detail the functionality of the database. Understand the scope of the project as well as the logic in the functions, from which the requirements of the database are formed.
  • After having enough business requirements, the business analyst will build an associated entity model with the associations between the respective entities and attributes. This model will describe real-world data associated with the business operations of the organization that uses it, focusing only on the logical nature of the data representation, caring about what is represented, not about what is being represented. Pay attention to how they perform.
  • Once we have the ERD document to communicate with the user to correctly and completely define the information requirements of the system, we will need to map through the relational model to provide the concept to the user. closest to how data is organized in a computer.
  • The final step in database design is to design a physical database based on a given DBMS by constructing data files, their indexes, their structure, arrangement in storage device. The tables in the relational database determine the actual structure of the tables stored in the relational model.
  • The process is shown as shown below:

Database design steps

This article is referenced from many sources and from what I have learned, hopefully it will be useful for some of you who are just starting to get acquainted with databases. In the next section, I will write about how to design the relational entity model and map the relational model. Install database in database management system, create API, …. and many more things

Share the news now

Source : Viblo