Data Design Screencast

Three Types of Relationships in Database Design

  1. One to one relationship: each row in a table corresponds to zero or one rows in another table.
  2. One to many relationship: each row in a table corresponds to zero or more rows in another table.
  3. Many to many relationship: many rows in a table correspond to zero or more rows in another table.

Twitter Example

  1. One to one relationship: each user has one profile and that profile belongs to only that user.
  2. One to many relationship: each user has many tweets and those tweets only belong that user.
  3. Many to many relationship: many users can favorite many tweets.

Simple Table Representations

Table 1: User Table
User
userId - primary key
email
password
Table 2: Profile Table
Profile
profileId - primary key
userId
firstName
lastName
Table 3: Tweet Table
Tweet
tweetId - primary key
userId
tweetText
tweetDate
Table 4: Favorites Table
Favorite
userId
tweetId

Simple Graphical Representations

One to One Relationship

Each user has one profile and that profile belongs to just that user.

one to one relationship

One to Many Relationship

Each user has many tweets and those tweets belong just to that user.

one to many relationship

Many to Many Relationship

Many users can favorite many tweets.

many to many relationship