How do you store large amounts of information? How does a person store, say, 20000 users each with 4 different purchases? It does this using a database system. Below are two different types of database systems. We will be focusing on a relational database system, as a flat database systems are really only hypothetical these days… most everything we do will be relational in nature.
| Flat Database A flat database simply is a list of items. For example, if you just have a list of people have signed up for a newsletter. |
Relational Database A relational database combines flat databases (or tables) and links up entries from one flat database (table) with another. For example, if you have a flat database (or table) of newsletters and a flat database (or table) of people who have signed up for newspapers. Each entry (or row) in the list of people who signed up for newsletters could be linked to several entries in the newsletters flat database (or table). |
Database
A database is a collection of tables.
Table
A table is a bit like an excel spreadsheet; it has rows and columns. Each row is called a row, record, or entry (the terms are used interchangably). A column is like a “field.”
Field
Each table contains a number of fields. Each field has a type. For example, if I have a table that is meant to store customer information, I could call it “customerinformation” and give it the fields “id”, “firstname”, “lastname”, and “phonenumber”.
Rows/Records/Entries
Each Row is an entry in a table. For example, in the above customer example, an entry in the “customerinformation” table might have an id of 1, a firstname of jacob, a lastname of beasley, and a phone number of 612 210 7533.
Indexes
Each table “should” have an Index. In other words, something unique. You might set it to autoincrement, too. For example, when you are put on a school system, you are given a “school id” number. Same idea… everything is given an “index” so you can tell it apart.
Relational Database Example: Customer Orders
Lets say that we have a bunch of orders and each order, for whatever reason, can only have one product related to it. This would mean that we could have two tables: one that is orders and one that is products. Below, there are three orders. Two of the orders were for donuts and one was for a crescent roll:
Products Table
|
Orders Table
|
Relationship Types
Fundamentally, there are 4 relationship types you need to be familiar with: one-to-one, one-to-many, many-to-one, many-to-many.
One-To-One
Description
Customers
|
Purchases
|
One-To-Many
Allows you to have one table have many associations on another table. For example, a person may have received, say, 50 messages. Each user has a one-to-many relationship between themselves and the messages they have sent. They also have a one-to-many relationship between their user account the messages they have received.
Customers
|
Purchases
|
Many-To-One
Just like one-to-many above, but flip left and right around.
Many-To-Many
In some cases, you may have many of one table associated to many of another table. For example, you may have 50 employees and 10 different office locations. Each employee may work out of several office locations and each office location may have many employees, thus you have a many-to-many relationship between the office locations and the employees table. Below is a demonstration of what this might sort of look like. Below, Jacob works at all locations, Davy works at lakeville, and Rob works in san diego:
Employees Table
|
Employees_OfficeLocations Table
|
OfficeLocations Table
|
