Basics of Design Rules
To design a database schema, you need to remember the 7 formal rules and the very concept of normalization and denormalization. They are the basis of all design rules.
Let’s describe the 7 formal rules in more detail:
one-to-one relationship:
1.1) with a binding connection:
An example might be a citizen and his passport: any citizen must have a passport; a passport is one for every citizen
This link can be implemented in two ways:
1.1.1) in one entity (table):
Here the Citizen table represents an entity of a citizen, and the PassportData attribute (field) contains all the passport data of the citizen and cannot be empty (NOT NULL).
1.1.2) in two different entities (tables):
Here the Citizen table is an entity of a citizen, and the PassportData table is an entity of passport data of a citizen (the passport itself). The citizen entity contains an attribute (field) PassportID, which refers to the PassportData primary key. In its turn the PassportData entity contains an attribute (field) CitizenID which refers to the CitizenID primary key of the Citizen table. The PassportID field of the Citizen table cannot be empty (NOT NULL). It is also important here to maintain the integrity of the CitizenID field of the PassportData table to ensure a one-to-one relationship. In other words, the PassportID field of the Citizen table and the CitizenID field of the PassportData table must refer to the same records as if they were the same entity (table) presented in 1.1.1.
1.2) with an optional link:
An example could be a person with or without a passport of a particular country. In the first case he will be a citizen of the country in question, and in the second case he will not.
This link can be implemented in two ways:
1.2.1) in one entity (table):
Person table is an entity of a person, and PassportData attribute (field) contains all his passport data and can be empty (NULL).
1.2.2) in two entities (tables):
The Person table is an entity of a person, and the PassportData table is an entity of a person’s passport data (the passport itself). The Person entity contains an attribute (field) PassportID, which refers to the primary key of the PassportData table. In turn the PassportData entity contains an attribute (field) PersonID which refers to the primary key PersonID of the table Person. The PassportID field of the Person table can be empty (NULL). It is also important here to maintain the integrity of the PersonID field of the PassportData table. This is necessary to ensure a one-to-one relationship. The PassportID field of the Person table and the PersonID field of the PassportData table must refer to the same records as if they were the same entity (table) shown in 1.2.1. Or the field data must be undefined, that is, contain NULL.
the relation of many to one:
This relation can be viewed mirror to the one-to-many relation above. In other words, the relation of the entity “children” to the entity “parents”, where the binding relation will be if the child has at least one parent. If all children are involved, including those in orphanages, the relation will be with an optional relation.