Relational Schema Design Google Professional Data Engineer GCP

  1. Home
  2. Relational Schema Design Google Professional Data Engineer GCP

Before delving into designing a schema, lets look at the properties that makes a good Database .

ACID properties

  • Atomic : Transaction is a single unit, either succeeds or fails completely.
  • Consistent : DB is consistent before and after the transaction.
  • Isolated : reading and writing to multiple tables at the same time.
  • Durability : once transaction is committed, will remain committed even in a system failure.


For a good Relational DB schema, following are important

  • Relationship between entities
  • Normalisation Form

Relationship Types

One to One –

  • one record is associated with one and only one record in another table.
  • Both tables have same Primary Key to identify the record.
  • Get complete data from both the tables by a join on this primary key .


One to Many –

  • Each record in first Table have many linked records in second Table, second table rows have only one corresponding record in first Table.
  • For data, put the primary key of first table as foreign key of the second table

Many To Many

  • Multiple records in a table are linked to multiple records of another table.
  • It is usually not allowed in RDBMS



It reduces redundancy from a relation or set of relations in DB .

  • 1 NF — There has to be a key that uniquely identifies a record/ row
  • 2 NF — should be in 1 NF and every non-key column should be fully dependent on the primary key
  • 3 NF — it should be in 2-NF and Non key columns are independent of each other