How to Organize the Data for Your MySQL Database - dummies

How to Organize the Data for Your MySQL Database

By Steve Suehring, Janet Valade

MySQL is a Relational Database Management System (RDBMS), which means the data is organized into tables. RDBMS tables are organized like other tables that you’re used to — in rows and columns, as shown in the following table.

Column 1 Column 2 Column 3 Column 4
Row 1
Row 2
Row 3
Row 4

The individual cell in which a particular row and column intersect is called a field.

The focus of each table is an object (a thing) that you want to store information about. Here are some examples of objects:

  • Customers

  • Products

  • Companies

  • Animals

  • Cities

  • Rooms

  • Books

  • Computers

  • Shapes

  • Documents

  • Projects

  • Weeks

You create a table for each object. The table name should clearly identify the objects that it contains with a descriptive word or term, based on the following guidelines:

  • The name must be a character string, containing letters, numbers, underscores, or dollar signs, but no spaces.

  • It’s customary to name the table in the singular form. Thus, a name for a table of customers might be Customer, and a table containing customer orders might be named CustomerOrder.

  • The difference between uppercase and lowercase is significant on Linux and Unix, but not on Windows. CustomerOrder and Customerorder are the same to Windows — but not to Linux or Unix. That said, it’s best to be sensitive to case in the event that you ever need to change hosting platforms.

In database talk, an object is an entity, and an entity has attributes. In the table, each row represents an entity, and the columns contain the attributes of each entity. For example, in a table of customers, each row contains information for a single customer. Some of the attributes contained in the columns might include first name, last name, phone number, and age.

Follow these steps to decide how to organize your data into tables:

  1. Name your database.

    Assign a name to the database for your application. For instance, you might name a database containing information about households in a neighborhood HouseholdDirectory.

  2. Identify the objects.

    Look at the list of information that you want to store in the database. Analyze your list and identify the objects. For instance, the HouseholdDirectory database might need to store the following:

    • Name of each family member

    • Address of the house

    • Phone number

    • Age of each household member

    • Favorite breakfast cereal of each household member

    When you analyze this list carefully, you realize that you’re storing information about two objects: the household and the household members. The address and phone number are for the household, in general, but the name, age, and favorite cereal are for each particular household member.

  3. Define and name a table for each object.

    For instance, the HouseholdDirectory database needs a table called Household and a table called HouseholdMember.

  4. Identify the attributes for each object.

    Analyze your information list and identify the attributes you need to store for each object. Break the information to be stored into its smallest reasonable pieces. For example, when storing the name of a person in a table, you can break the name into first name and last name.

    Doing this enables you to sort by the last name, which would be more difficult if you stored the first and last name together. You can even break down the name into first name, middle name, and last name, although not many applications need to use the middle name separately.

  5. Define and name columns for each separate attribute that you identify in Step 4.

    Give each column a name that clearly identifies the information in that column. The column names should be one word, with no spaces. For example, you might have columns named firstName and lastName or first_name and last_name.

    MySQL and SQL reserve some words for their own use, and you can’t use those words as column names. The words are currently used in SQL statements or are reserved for future use. You can’t use ADD, ALL, AND, CREATE, DROP, GROUP, ORDER, RETURN, SELECT, SET, TABLE, USE, WHERE, and many, many more as column names.

  6. Identify the primary key.

    Each row in a table needs a unique identifier. No two rows in a table should be exactly the same. When you design your table, you decide which column holds the unique identifier, called the primary key.

    The primary key can be more than one column combined. In many cases, your object attributes don’t have a unique identifier. For example, a customer table might not have a unique identifier because two customers can have the same name.

    When you don’t have a unique identifier column, you need to add a column specifically to be the primary key. Frequently, a column with a sequence number is used for this purpose. For example, here the primary key is the cust_id field because each customer has a unique ID number.

A Sample of Data from the Customer Table
cust_id first_name last_name phone
27895 John Smith 555-5555
44555 Joe Lopez 555-5553
23695 Judy Chang 555-5552
29991 Jubal Tudor 555-5556
12345 Joan Smythe 555-5559
  1. Define the defaults.

    You can define a default that MySQL assigns to a field when no data is entered into the field. You don’t need a default, but one can often be useful. For example, if your application stores an address that includes a country, you can specify U.S. as the default. If the user doesn’t type a country, MySQL enters U.S.

  2. Identify columns that require data.

    You can specify that certain columns aren’t allowed to be empty (also called NULL). For instance, the column containing your primary key can’t be empty. If no value is stored in the primary key column, MySQL doesn’t create the row and returns an error message.

    The value can be a blank space or an empty string (for example, ), but some value must be stored in the column. You can set other columns, in addition to the primary key, to require data.

Well-designed databases store each piece of information in only one place. Storing it in more than one place is inefficient and creates problems if you need to change information. If you change information in one place but forget to change it in another place, your database can have serious problems.