Introduction
Database Design is a crucial aspect to learn as a Developer. It is one of the first steps during the development of an App, and it contributes to the high performance and efficiency of the application. In designing a database, there are basic terminologies one needs to get familiar with. These terms are divided into four: Value-related terms, Structure-related terms, Relationship-related terms, and Integrity-related terms. Understanding these terms makes the design process easier and expresses the concept of the relational database model.
The conclusion of design flows naturally from the data; we should not shrink from it; we should embrace it and build on it. — Michael Behe
Value-related terms
Data: This is the value stored in the database. It is static and remains in the same state except modified. Data can be in various forms like names, countries, numbers, etc.
Information: This is the data that is being processed to make it meaningful and useful to work it. When data is being retrieved from the database, it turns into meaningful Information.
Null: This is simply the missing or unknown value in the database. It is neither zero nor empty string as these can represent useful information. It is represented as null in a table or N/A in some cases where the value is Non-applicable.
Structure-related terms
Table: Data in a relational database are stored in tables. A table consists of fields and records which represent a specific subject. Every table consists of a field known as the primary key which is used to identify uniquely each of its records.
Fields: They are also known as attributes in a relational database. They are the structures that store data in a table and data can be retrieved from them to get meaningful Information.
Records: They are also known as tuples in a relational database. They consist of the entire sets of fields in a table. Every record is identified uniquely by a primary key, and they are important in understanding table relationships in a relational database.
- Keys: Keys are special fields within a table in the database that identifies each of its records uniquely. The two types of keys commonly used in a relational database are; Primary Key and Foreign Key. Every table consists of the primary key( ID) which identifies uniquely each of its records. A primary key becomes a foreign key when one establishes a relationship between two tables and introduces the primary key of the first table to the second table where it becomes foreign.
Relationship-related terms
A relationship is established between two or more tables when the records from the first table are associated or linked with the second table through the use of the primary keys. In a relational database, we have three major types of relationships which are: One to One relationship, One to many relationship, and many to many relationship.
One to One relationship: A pair of tables establishes a one-to-one relationship when a single record in the first table is related to one or zero records in the second table and vice versa.
One to Many relationship: A pair of tables establish a one-to-many relationship when a single record in the first table is related to zero, one or many records in the second table and a single record in the second table is related to one or zero records in the first table.
Many to Many relationship: A pair of tables establish a many-to-many relationship when a single record in the first table is related to one, zero, or many records in the second table and vice versa.
Integrity-related terms
Data Integrity
Data integrity refers to the accuracy, validity, and consistency of data in the database. It is one of the most crucial concepts in database design as it helps to avoid duplicates and reduces redundancy in data. In a relational database, there are three major types of data Integrity which are: Table-level Integrity, Field-level Integrity, and Relationship-level Integrity. Each type ensures accurate, valid, and consistent data at each level in the database.
Conclusion
We discussed the basic terms used when designing a database which is split across four categories. Knowledge and usage of these terms are important in designing a good database.
Reference: Database Design for Mere Mortals by Michael J. Hernandez