HTML, XHTML and CSS All-In-One for Dummies - Andy Harris [246]
If your name is really Zebulon Zoom, you have a very cool name — a good sign in the open-source world, where names like Linus and Guido are really popular. I figure the only reason I’m not famous is my name is too boring. I’m thinking about switching to a dolphin name or something. (Hi, my name is “Andy Squeeeeeeek! Click Click Harris.”)
Relational databases solve this problem by forcing each record to be the same length. Just for the sake of argument, imagine that every record takes exactly 100 bytes. You would then be able to figure out where each record is on the disk by multiplying the length of each record by the desired record’s index. (Record 0 would be at byte 0, record 1 is at 100, record 342 is at 34200, and so on.) This mechanism allows the computer to keep track of where all the records are and jump immediately to a specific record, even if hundreds or thousands of records are in the system.
My description here is actually a major simplification of what’s going on, but the foundation is correct. You should really investigate more sophisticated database and data structures classes or books if you want more information. It’s pretty cool stuff.
The length of the record is important because the data types of a record’s fields determine its size. Numeric data (integers and floating-point values) have a fixed size in the computer’s memory. Strings (as used in other programming languages) typically have dynamic length. That is, the amount of memory used depends on the length of the text. In a database application, you rarely have dynamic length text. Instead, you generally determine the number of characters for each text field.
Defining a primary key
When you turn the contact data into an actual database, you generally add one more important field. Each table should have one field that acts as a primary key. A primary key is a special field that’s
♦ Unique: You can’t have two records in a table with the same primary key.
♦ Guaranteed: Every record in the table has a value in the primary key.
Primary key fields are often (though not always) integers because you can easily build a system for generating a new unique value. (Find the largest key in the current database and add one.)
In this book, each table has a primary key. They are usually numeric and are usually the first field in a record definition. I also end each key field with the letters ID to help me remember it’s a primary key.
Primary keys are useful because they allow the database system to keep a Table of Contents for quick access to the table. When you build multitable data structures, you can see how you can use keys to link tables together.
Defining the table structure
When you want to build a table, you begin with a definition of the structure of the table. What are the field names? What is each field’s type? If it’s text, how many characters will you specify?
The definition for the e-mail contacts table may look like this:
Field Name
Type
Length (Bytes)
ContactID
INTEGER
11
Name
VARCHAR
50
Company
VARCHAR
30
VARCHAR
50
Look over the table definition, and you’ll notice some important ideas:
♦ There’s now a contactID field. This field serves as the primary key. It’s an INTEGER field.
♦ INTEGERs are automatically assigned a length. It isn’t necessary to specify the size of an INTEGER field (as all INTEGERs are exactly 11 bytes long in MySQL).
♦ The text fields are all VARCHARs. This particular table consists of a lot of text. The text fields are all stored as VARCHAR types.
♦ Each VARCHAR has a specified length. Figuring out the best length can be something of an art form. If you make the field too short, you aren’t able to squeeze in all the data you want. If you make it too long, you waste space.
VARCHAR isn’t quite variable length. The length is fixed, but extra spaces are added. Imagine that I had a VARCHAR(10) field called userName. If I enter the