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.