Designing of Database

The first step in creating a database is to design it. You create the database and its tables according to the design you developed.

images/articles/mysql/designing-of-database.jpg

Designing the database includes identifying the data that you need and organizing the data in the way that the database software requires. As you plan your database design, you will also need to decide on a primary key for each table and how tables relate to one another.

Choosing Data

To design a database, you first must identify what information belongs in it. The database must contain the data needed for the website to perform its purpose. For example,

  • An online catalog needs a database containing product information.
  • An online order application needs a database that can hold customer information and order information.
  • A travel website needs a database with information on destinations, reservations, fares, schedules, and so on.

In many cases, your application might include a task that collects information from the user. For example, a customer database might collect the following customer information:

  • Name
  • Address
  • Phone number
  • Birth Date
  • E-mail address

Organizing Data

MySQL is a Relational Database Management System (RDBMS), which means the data is organized into tables. RDBMS tables are organized in rows and columns. The individual cell in which a particular row and column intersect is called a field.

The focus of each table is an object (thing) that you want to store information about. For example, 

  • Customers
  • Products
  • Companies
  • Animals
  • Cities
  • Rooms
  • Books
  • Computers
  • Documents
  • Projects

You create a table for each object. The table name should clearly identify the objects that it contains with a descriptive word or term.

The name must be a character string, containing letters, numbers, underscores, or dollar signs, but no spaces. For example, a name for a table of customers might be Customer, and a table containing customer orders might be named CustomerOrder.

In terms of database, 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.

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.

Relationships Between Tables

Some tables in a database are related. A row in one table is related to several rows in another table. You need a column to connect the related rows in different tables. In many cases, you include a column in one table to hold data that matches data in the primary key column of another table.

For example, one table contains the customer information, such as name, address, and phone number. Each customer can have from zero to many orders. You could store the order information in the table with the customer information, but a new row would be created each time the customer placed an order, and each new row would contain all the customer’s information. You can much more efficiently store the orders in a separate table (say CustomerOrder). In the CustomerOrder table, you include a column (cust_id) that contains the primary key from a row in the Customer table so the order is related to the correct row of the Customer table.

There are three kinds relationships:

  1. one-to-one
  2. one-to-many
  3. many-to-many

The simplest of these is the one-to-one relationship. A one-to-many relationship is somewhat more complex than a one-to-one relationship. Many-to-many relationships are the most complex. In the many-to-many relationship, you have to create an intersection relation.