Let us understand details about relations and different types of relationships we typically use.
In RDBMS - R stands for Relational.
In the transactional systems, tables are created using normalization principles. There will be relations or tables created based on relationships among them.
Here are the typical relationships among the tables.
1 to 1
1 to many or many to 1 (1 to n or n to 1)
many to many (m to n)
To enforce relationships we typically define constraints such as Primary Key and Foreign Key.
Here is the typical process we follow from requirements to physical database tables before building applications.
Identify entities based up on the requirements.
Define relationships among them.
Create ER Diagram (Entity Relationship Diagram). It is also called as Logical Data Model.
Apply Normalization Principles on the entities to identify tables and constraints to manage relationships among them.
Come up with Physical Data Model and generate required DDL Scripts.
Execute the scripts in the database on which applications will be eventually build based up on business requirements.
Logical modeling is typically done by Data Architects.
Physical modeling is taken care by Application Architect or Development lead.
Let us go through data model related to HR and OE systems.
Identify the relationships between the tables.
Differentiate between transactional tables and non transactional tables.