One-to-Zero/One Relationships

Table of Contents

Specializations

Similar to inheritance in object-oriented universe, one-to-zero-or-one specialization in relational models implies propagation of one table’s primary key as both primary and foreign key in other (dependent) tables. Initial table holds fields common to all specialized tables. Example below has two specializations:

  • Users can be Administrators, Managers or Clerks
  • related user Entries can be Offices, Reserves or Bands.

specialization

Extensions

Another one-to-zero-or-one relationship that looks more like an extension to an existing object. Some users may have an address. Instead of keeping a sparse Users table with address fields that may be used by just a few, we split it and separate Address as extension:

extension

Independent Tables

Any record’s existence does not depend on any other table records. If it has no PKs, it must also have no FKs. Otherwise, none of its PK keys is an FK. For the shapes below:

  • Users is independent table –> round shape corners, specific big icon.
  • Address is a dependent table (exists only if user) –> rectangular shape corners, small table icon.
  • Provinces is independent (and also lookup) –> specific icon.

lookup-table

Lookup Tables

With a key-display mapping. Related tables use its PK as FK, but rather another of its column for display, through this mapping. Provinces lookup table below has a province_code –> province_name mapping:

lookup-table2