Tables as Relations¶
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.