First lets start with some basic terminology

Some Relational Database basics before we begin:

Database = A collection of structured tables designed to store data

Table = A database object that stores data by organizing it into rows and columns

Field = Each column in a database table is called a field

Record = Each row in a database table is called a record

We want to create databases that will:

1.Store our data

2.Let us interact and ask questions of the data

3.Retrieve or export the data

In order to do this, we need to have a properly designed database

Example Situation #1:

A teacher puts out a sign up sheet during back to school night to allow parents to donate various classroom supplies.  The parents of the class fill out the sign up sheet and it looks like this:

The next day the teacher types up the donation sheet and puts it in Excel.  The teacher decides that he wants to try to build a database to keep track of what each person donated.  After noticing that at most someone is donating 3 items he builds a database table that looks like this:

In Excel:

Database:

The teacher is pleased.  He set out to create a database to keep track of what each parent donated.  Given a parent name he can easily see their donations.  However, when he wants to see all the parents that have donated tissues he ran into some problems. 

Problems:

•The table answers questions in 1 direction only (given a parent you can easily return donations)

•To answer the question in the other direction (what parents donated tissues?) you need to search across 3 different fields

•The table was designed without recognizing we are dealing with 2 different classes

---

With a parent name provided as input (ex: Greg Carter), a record in the table can be identified.  Once we have identified a record, the values for all fields in that record can be retrieved.

So, this table works to answer questions like this:

“What donations did Greg Carter provide?”

Example Situation #1 Takeaways:

1.Do not let an existing form dictate your table design (a form can be simple like a paper signup sheet or more complex like the interface of an application)

2.You must get to know your data to be able to correctly identify how many classes of data you have

3.Be careful when designing your table to answer a particular question – will you ever want to ask that question in the opposite direction?

4.Don’t rush to simply load given data into a table – take the time to think through design

---

Repeated Information:

•Another common problem is storing the same piece of information several times

•If you find this happening, you may be placing too much emphasis on an existing form

Example:

A business’s paper order form has customer name, address, phone number.  We wouldn’t want to create a database table to keep storing all the information for every single order.  It is inefficient and will most likely lead to inconsistencies and future problems. 

If you see repeated information in a table, it should be a huge red flag for you to re-examine the design. 

Designing for a single report:

•You can’t allow an existing form to determine your table design

•Similarly, you can’t allow a report to determine your table design

•Think about reports as nicely presented data coming out of your database

Back to Main Course Page: Course