Mapping your data structure
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:
Table columns describe the data
Each table contains a number of columns.
A column stores an attribute of the table item.
people might have attributes
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.
Choose one or more of the following tables
and draw a visual plan for its structure:
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 to store strings
First names, last names, pet names, celebrity crush names.
Stored text can have spaces, so could store a street address.
- Long Text
Can also store paragraphs of text, like someone’s personal bio.
Stored as ISO8601 strings (“YYYY-MM-DD HH:MM:SS.SSS”)
integer to store whole numbers
Stored using 1 for true and 0 for false
A number from 0 to something really big
- Stock Count
Positive or negative whole number
Stored as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.
real to store decimals
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
numeric to store numbers
Use to store a mix of integer and real numbers
blob to store other weird data
Can be stored as hex data, though it’s not ideal.
Can be stored as binary data, but a bad idea.
Challenge: Data Types
Assign a data type to each of your table columns, from:
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
Tables are planned using table maps and visual lines between.
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!