MySQL is a relational database.
An important feature of relational systems is that a single database can be
spread across several tables as opposed to our flat-file phone book example.
Related data is stored in separate tables and allows you to put them together by
using a key common to both tables. The key is the
relation between the tables. The selection of a primary
key is one of the most critical decisions you'll make in designing a new
database. The most important concept that you need to understand is that you
must ensure the selected key is unique. If it's possible that two records past,
present, or future share the same value for an attribute, don't use them as a
primary key. Including key fields from another table to form a link between
tables is called a foreign key relationship, like
a boss to employees or a user to a purchase. The relational model is very useful
because data is retrieved easier and faster.
Relationship Types
Databases relationships are quantified with the following
categories:
-
One-to-one relationships
-
One-to-many relationships
-
Many-to-many relationships
We'll discuss each of these relationships and provide an
example. If you think of a family structure when thinking about relationships,
you're ahead of the game. When you spend time alone with one parent, that's a
specific type of relationship; when you spend time with both your parents,
that's another one. If you bring in a significant partner and all of youyour
parents, you, and your partnerall do something together, that's another
relationship. This is identical to the bucket analogy. All those different types
of relationships are like specific buckets that hold the dynamics of your
relationships. In the database world, it's the data you've created.
In a one-to-one relationship, each item is related to one and only one
other item. Within the example of a bookstore. A one-to-one relationship exists
between users and their shipping addresses.
Normalization
Thinking about how your data is related and the most efficient
way to organize it is called normalization. Normalization of data is breaking it apart based on the
logical relationships to minimize the duplication of data. Generally, duplicated
data wastes space and makes maintenance a problem. Should you change information
that is duplicated, there's the risk that you miss a portion and you risk
inconsistencies in you database.
It's possible to have too much of a good thing though:
databases placing each piece of data in their own tables would take too much
processing time and queries would be convoluted. Finding a balance in between is
the goal.
While the phone book example is very simple, the type of data
that you process with a web page can benefit greatly from logically grouping
related data.
Let's continue with the bookstore example. The site needs to
keep track of the user's data, including login, address, and phone number, as
well as information about the books, including the title, author, number of
pages, and when each title was purchased.