Table Planning

Database Diagrams

The first step in building a database
is creating a map of the data.

Tables contain rows of data

A table should contain information
about a single set of data, eg:

people, cities, purchases, users, scores

Table columns describe the data

Each table contains a number of columns.
A column stores an attribute of the table item.

The table people might have attributes
first_name, last_name, birth_date, email

Names use lowercase and underscores

Table and column names should be all lowercase
and use underscores instead of spaces.

Table names are always plural.

Use table diagrams to plan

To plan a table visually, write the name of the table
and list the table’s attributes underneath.

Challenge: Attributes

Choose one or more of the following tables
and draw a visual plan for its structure:

users, scores, todo_items

Data Types

Table columns each have a defined data type
to restrict the information which can be stored.

SQLite has only five data types

In SQLite3, the available data types are:

text, numeric, integer, real, blob

Use text to store strings

• Name
  First names, last names, pet names, celebrity crush names.
• Address
  Stored text can have spaces, so could store a street address.
• Long Text
  Can also store paragraphs of text, like someone’s personal bio.
• Date
  Stored as ISO8601 strings (“YYYY-MM-DD HH:MM:SS.SSS”)

Use integer to store whole numbers

• Boolean
  Stored using 1 for true and 0 for false
• Score
  A number from 0 to something really big
• Stock Count
  Positive or negative whole number
• Date
  Stored as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.

Use real to store decimals

• Date
  Stored as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.
• Bank Balance
  Positive or negative number with decimal places

Use numeric to store numbers

Use to store a mix of integer and real numbers

Use blob to store other weird data

• Photos
  Can be stored as hex data, though it’s not ideal.
• Documents
  Can be stored as binary data, but a bad idea.

Challenge: Data Types

Assign a data type to each of your table columns, from:

text, numeric, integer, real, blob

Relationships between tables

We can represent how data links together, for example:

• A parent has children.
• A person has an address.
• A customer has purchases.
• A user has friends.

There are three types of relationships

• One to One
  A user has a profile page, and a profile page belongs to only one user.
• One to Many
  A customer has many purchases, but a purchase belongs to only one customer.
• Many to Many
  A purchase has many bought items, and an item can be in many purchases.

Relationships are drawn as lines between tables

A table can have one, none, or many
lines going to other tables.

Every table needs a unique ID column

This column is called the Primary Key.
Its type is integer and its value is auto-generated.

Linked data is stored using the row’s ID

A linked data column is know as a Foreign Key.
Its type is integer and you must specify the ID to store.

Challenge: Relationship Diagrams

Draw these tables, their attributes
and map their relationships:

users, friendships, photos, albums

Challenge: Identify Primary and Foreign Keys

Mark the primary key column with (PK),
and mark the foreign key columns with (FK)

What we learned about table planning

• Planning
  Tables are planned using table maps and visual lines between.
• Relationships
  Tables can have related data using primary and foreign keys.
• Primary Keys
  Are columns which uniquely identify a row item.
• Foreign Keys
  Are columns which store the primary key of an item in another table.

Table Planning: Complete!

Take me to the next chapter!