Designing a Database

This example will be to Create a MySQL Database - the similar steps are used throughout...

Determine the Tables needed
Determine each tables Attributes
Specify Primary Keys
Create Relationships

Determine the Table(s)

We're going to take a very simple example of the guest book which was previously using a text file. As we are only need the one table at this time. Breaking down the tables and their individual attributes is one of the more difficult things to do. Anticipating what information will be stored requires a bit of understanding how your system will be used. While it takes some time, it is quite possible. Look for items that repeat across multiple records, or are only within a select group of possible answers. If you see these, then using multiple tables and joining them via a relationship is a good idea.

Determine the Table's Attributes

Each record will be what someone left for us. For this example, we will want to track the person's name, their comment, and when they left it; so we can later display it on the page. We don't need but the one table, and these two fields (as far as we can tell).

MySQL Data Types

Numbers - Any time you need to store a number you can use one of the numeric data types. We should use these when we need to store numbers which we will work on with some sort of math.
- Integer (tiny int, big int, integer) (signed or unsigned) (auto number optional) - These are your whole numbers (those without decimal values)
- Float - These are used to store numbers with decimals in them. These numbers are not always precise.
- Decimal - These numbers are more precise versions of decimal numbers. You have to specify how many places the number can take up, as well as how many places the to the right of the decimal you will need.
Text - There are several types of text fields we can use. Any time we need non numeric values, we should use one of the text fields.
- Char - Fixed number of characters with a maximum of 255 characters. Good when you know how many characters a field would have, like a phone number, SSN, et cetera.
- VarChar - Used when you need to store a variable number of characters, up to a maximum of 255 characters. You specify how many you want store, and the system will allow you to store up to that number. Good for fields where the number of characters will change from row to row - such as names and addresses.
- Text - This field can be almost any length you want. It does have a limit of 65,535 characters (about 32 pages to plain text). You don't specify the length, it just gets used as necessary. There are long versions which can store 4,294,967,295 characters as well (about 2,147,483 pages to plain text).
BLOB - (Binary Large Object) Used to store large sections of binary data. (Think of storing files inside the database.)
Date / Time - Various types of fields exist for storing a date, time, or both.

When do I use a Number vs Text

If we have a number, which we don't use as a number (like a phone number, or SSN) then we should use a text data type.

Data Types We Need

The person's name: We don't know how many character's the person will need, but most names will be under 30 characters, even for long names. Therefore, looking at our text field types we can determine that a VARCHAR will be the best choice.

The guest book entry: While this could be short ("Great Site!") it could also easily be longer than 255 characters, especially if someone wanted to add HTML tags in their site. Therefore, we should look at using a TEXT data type.

Entry entered when: Obviously we need to use the date data type for this field.

What about the Primary Key?

A primary key needs to be a unique value so we can reference it later on when necessary. When we look at the fields we have already, I see that none of them can be guaranteed to be unique. Two people could have the same name (I'm the third in my family), and two people could leave the same comment, and obviously multple comments can be left on the same day.

Therefore we need to look to a new data field. We will create a field called id that can be used as a unique identifier. So we don't have to create our own unique value, we will assign this value the type of integer, and make it an autonumber. An autonumber is a built in type which auto increments itself for each new record, so there are no duplicate values.

Any Relationships?

Relationships will exist between multiple tables. As we only have one table - there is no need to attempt to create a relationship in this example.