HTML, XHTML and CSS All-In-One for Dummies - Andy Harris [245]
♦ A collection of records is a table. All records in a table have the same field definitions but can have different values in the fields.
♦ A bunch of tables makes a database. Real-world data doesn’t usually fit well in one table. Often, you’ll make several different tables that work together to describe complex information. The database is an aggregate of a bunch of tables. Normally, you restrict access to a database through a user and password system.
Determining the fields in a record
If you want to create a database, you need to think about what entity you’re describing and what fields that entity contains. In the table in the preceding section, I’m describing e-mail contacts. Each contact requires three pieces of information:
♦ Name: Gives the name of the contact, in 50 characters or less
♦ Company: Describes which company the contact is associated with, in 30 characters or less
♦ E-mail: Lists the e-mail address of the contact, in 50 characters or less
Whenever you define a record, begin by thinking about what the table represents and then think of the details associated with that entity. The topic of the table (the kind of thing the table represents) is the record. The fields are the details of that record.
Before you send me e-mails about my horrible data design, know that I’m deliberately simplifying this first example. Sure, it should have separate fields for first and last name, and it should also have a primary key. I talk about these items later in this minibook, as well as in the section “Defining a primary key,” later in this chapter. If you know about these items already, you probably don’t need to read this section. For the rest of you, you should start with a simple data model, and I promise to add all those goodies soon.
Introducing SQL data types
Each record contains a number of fields, which are much like variables in ordinary languages. Unlike scripting languages, such as JavaScript and PHP (which tend to be freewheeling about data types), databases are particular about the type of data that goes in a record.
Table 1-1 illustrates several key data types in MySQL (the variant of SQL used in this book).
Table 1-1 MySQL Data Types
Data Type
Description
Notes
INT (INTEGER)
Positive or negative integer (no decimal point)
Ranges from about –2 billion to 2 billion. Use BIGINT for larger integers.
DOUBLE
Double-precision floating point
Holds decimal numbers in scientific notation. Use for extremely large or extremely small values.
DATE
Date stored in YYYY-MM-DD format
Can be displayed in various formats.
TIME
Time stored in HH:MM:SS format
Can be displayed in various formats.
CHAR(length)
Fixed-length text
Always same length. Shorter text is padded with spaces. Longer text is truncated.
VARCHAR(length)
variable-length text
Still fixed length, but trailing spaces are trimmed. Limit 256 characters.
TEXT
Longer text
Up to 64,000 (roughly) characters. Use LONGTEXT for more space.
BLOB
Binary data
Up to 64K of binary data. LONGBLOB for more space.
I list only the most commonly used data types in Table 1-1. These data types handle most situations, but check the documentation of your database package if you need some other type of data.
Specifying the length of a record
Data types are especially important when you’re defining a database. Relational databases have an important structural rule: Each record in a table must take up the same amount of memory. This rule seems arbitrary, but it’s actually very useful.
Imagine that you’re looking up somebody’s name in a phone book, but you’re required to go one entry at a time. If you’re looking for Aaron Adams, things will be pretty good, but what if you’re looking for Zebulon Zoom? This sequential search would be really slow because you’d have to go all the way through the phone book to find Zebulon. Even knowing