Relational Databases In Ten Minutes!

Database Crash Course
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

id name price
1 donut $3
2 crescent roll $2
Orders Table

id products_id status date
1 1 done 9/13/2009
2 2 done 9/13/2009
3 1 still need to ship 9/14/2009


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

id name phone sex
1 john smith 555-555-5555 male
2 ivy, poison 555-654-6456 female
Purchases

id customer_id (make it an index along with id) amount date
1 1 $3245 9/13/2009
2 2 $234 9/13/2009

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

id name phone sex
1 john smith 555-555-5555 male
2 ivy, poison 555-654-6456 female

Purchases

id customer_id (but not an index so multiple products can be associated) amount date
1 1 $3245 9/13/2009
2 2 $234 9/13/2009

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

id firstname lastname
1 jacob beasley
2 davy stiles
3 rob mohr
Employees_OfficeLocations Table

index employee_id index location_id
1 1
1 2
1 3
2 2
3 3
OfficeLocations Table

id city state
1 farmington mn
2 lakeville mn
3 san diego ca

  • Share/Bookmark



No Comments


You can leave the first : )



Leave a Reply

Your email address will not be published. Required fields are marked *