The design uses various modes to build all the relevant processes of new transactions by combining existing concepts or projects.
One-to-multiple mode
A typical parent-child relationship. Multiple tuples (rows) in a secondary object correspond to one of the primary objects.
One-to-one mode
In the concept layer, a one-to-one relationship is quite rare. Usually, one-to-one relationship is used for SQL odd or the physical layer to partition data of some performance or security factors.
A one-to-one relationship uses the primary keys of two entities to connect two entities. Because the primary key must be unique, all parties to the link must be a single tuples.
Multi-to-Multi-Mode
In many-to-many relationships, the parties to the relationship may involve multiple tuples (rows) of the other party)
Many-to-many relationships are almost all optional.
To implement multi-to-many relationships in SQL DDL, you must use the third table, also known as the associated table (sometimes called the joined table), to artificially create two one-to-multiple relationships between two entities.
When designing an attribute for an associated object, each attribute only describes many-to-many relationships rather than one of the main entities.
Parent/child Mode
Support Generalization
It is perfect for Object-Oriented Modeling in relational databases.
Parent-child relationships use one-to-one relationships to connect parent entities with one or more child entities. Extended parent entity with flexibility
The parent/child mode associates an optional one-to-one relationship between a primary key and another primary key.
Domain integrity Search Mode
Domain integrity lookup mode, which is formally called The Lookup Table mode. It is used to restrict valid attributes.
Recursive Mode
Recursive link mode (sometimes called self-reference, one-dollar or self-join relationship) is a mode of connecting itself.
Reflection or recursion is a one-to-multiple relationship between two tuples (rows) in the same object.
Ex: because a part may be built by multiple source parts or multiple parts in the next production process, a list of all materials is a more complex recursive mode.
PS: Concepts of Multi-to-Multi recursive relationships. graphs show that the relationship has multiple bases.
The associated entity needs to address the many-to-many relationship between the components in use and the components being assembled.
PS: the physical implementation of multiple-to-multiple recursive relationships must have an associated entity to solve multiple-to-multiple relationships, just like the relationship between multiple-to-many and two entities.
-- SQL Server 2008 Bible