Tuesday, March 19, 2024

Database Basics: Part 1

Use these bookmarks
to jump around the tutorial:

[How
Does My Data Get Stored?
]

[How
Do I Organize My Data?
]

[How
Does All of This Relate?
]

[What’s
Next?
]

So, you’ve decided that
you’re interested in using a database in your web development, huh? Well, then
the next step would be to learn about how the database works.

How
Does My Data Get Stored?

How the data is stored
in a
database is probably much simpler than you might think. Databases use a series
of Tables to store the data. A table simply refers to a two dimensional
representation of your data using columns and rows. For example:

 

John Smith jsmith@huh.com
Paul McCartney paul@beatles.com
Bill Murray gopher@caddyshack.com

So, then , how does the
database keep things straight? Well, first each database table is given a unique
name. Without a unique name the DBMS (DataBase Management System) would
get very confused.

Next, each column in the
table is given a unique name. In our example above it would be something like
first_name, last_name, email. This doesn’t mean each column that you name has to
be unique within the entire database. It only has to be unique within the table
you have created. Also notice that the names
don’t use any spaces. When naming tables and columns be sure to keep it simple
with letters and numbers. Spaces and symbols can be illegal characters that will
mess up the whole works, so if you need to clarify a name use the "_" instead of
spaces.

Let’s update our table
now:

Table name: contacts


first_name
last_name email
John Smith jsmith@huh.com
Paul McCartney paul@beatles.com
Bill Murray gopher@caddyshack.com

back to
top

How
Do I Organize My Data?

The next thing to
understand about your table is the Primary Key. The Primary Key simply
refers to a column in your table that is guaranteed unique. The Primary Key is
then used for the purposes of indexing your table which makes it much more
efficient to search, sort, link, etc.

So what is the Primary
Key in our example? Good question. There is none. In our example, there is
nothing that is going to be guaranteed unique. Obviously there are many people
that share the same last name and/or first name that may be added to the
database in the future. The email address is much more likely to be unique but
what if two people shared the same email?

To avoid the uncertainty
of using a data column as a primary key, many developers will create their own
column which contains a computer generated unique number, an ID number of sorts.
This way you don’t ever have to worry about its uniqueness since the database
knows not to ever use the same number twice.

Table name: contacts


contact_id

first_name
last_name email
1 John Smith jsmith@huh.com
2 Paul McCartney paul@beatles.com
3 Bill Murray gopher@caddyshack.com

 

How many tables should I
use? That depends on how you can logically break down your data. There is no
limit to the number of tables, or columns for that matter, you can create. Keep
in mind, though, that one huge table will be very inefficient while a bunch of
little tables can be nearly impossible to keep straight. The best solution
usually lies somewhere in the middle.

 

Here’s an example. Let’s
say our contact table stores contact information for a subscription database for
HTML Goodies. Now we need to store what newsletter(s) each person wants to
subscribe to. We could simply add another column in our contact table that would
store the name of the newsletter. This would allow us to save the information we
need but cause names and email addresses to be duplicated, once for each
different newsletter a person subscribes to. That would be highly inefficient.

 

What about making a
second table for the names of the newsletters. This way each newsletter name and
description would be stored only once.

 

Table name: newsletters


newsletter_id

name

description
1 Goodies to Go A newsletter for HTML fans.
2 Design Goodies A newsletter for web & graphic designers.

back to
top

How Does
It All Relate?

That’s great. I have the
people in one table and the newsletters in another table. How the heck am I
supposed to know who is subscribed to what?

This is the best part.
This is where the Relational Database gets its name. Relational Database?
You never mentioned that.

So, far you have learned
some of the basic elements of a database. Now you will learn how to take those
basic elements and make them relate to one another so that the information you
are storing remains logically linked together. Hence the new and improved
Relational Database.

Now, using our example
above we have a table of subscribers (contacts) and a table of newsletter
information (newsletters). So, how do we know who subscribed to what? We make
another table that links the two tables we already have together.

 

Table Name: contact_newsletter_link


contact_id

newsletter_id
1 2
2 1
2 2
3 1

This table then places a
link between the contact table and the newsletters table using each tables
unique ID number. So, by referring to the table above you can see that John
Smith has subscribed to Goodies to Go, Paul McCartney subscribed to Goodies to
Go & Design Goodies and Bill Murray subscribed to Design Goodies.

But what about that
Primary Key thing you mentioned earlier? The ID numbers are used more than once.
Nothing is unique.

That’s true. In this
instance we are using Foreign Keys. A Foreign Key basically means that
the number used in a Foreign Key column is not necessarily unique to the table
it is in but is unique to the table it is referring to. In this case
contact_id is unique to the contact table but not necessarily unique to the
contact_newsletter_link table.

We now have the basics
of a relational database.

back to
top


What’s Next?

Now that you have an
understanding of the basic structure of a relational database we will examine
the tables and the data a bit more closely.

In Part 2 of this series
we will:

  • Learn about the most
    common data types

  • Learn some basic SQL
    commands to create tables

  • Learn how to create a
    database and tables with a tool like Access

back to
top

Next >>

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Popular Articles

Featured