How to Design an SQL Database

The first step to designing any database in SQL is to identify what to include and what not to include. The next steps involve deciding how the included items relate to each other and then setting up tables accordingly.

To design a database in SQL, follow these basic steps:

1. Decide what objects you want to include in your database.

2. Determine which of these objects should be tables and which should be columns within those tables.

3. Define tables based on how you need to organize the objects.

   Optionally, you may want to designate a table column or a combination of columns as a key.

Step 1: Define objects

The first step in designing a database is deciding which aspects of the system are important enough to include in the model. Treat each aspect as an object and create a list of all the objects you can think of. At this stage, don’t try to decide how these objects relate to each other. Just try to list them all.

When you have a reasonably complete set of objects, move on to the next step: deciding how these objects relate to each other. Some of the objects are major entities that are crucial to giving you the results you want. Other objects are subsidiary to those major entities. Ultimately you may decide that some objects don’t belong in the model at all.

Step 2: Identify tables and columns

Major entities translate into database tables. Each major entity has a set of attributes — the table columns. Many business databases, for example, have a CUSTOMER table that keeps track of customers’ names, addresses, and other permanent information. Each attribute of a customer — such as name, street, city, state, zip code, phone number, and e-mail address — becomes a column (and a column heading) in the CUSTOMER table.

If you’re hoping to find a set of rules to help you identify which objects should be tables and which of the attributes in the system belong to which tables, think again: You may have some reasons for assigning a particular attribute to one table and other reasons for assigning the same attribute to another table. You must base your judgment on two goals:

• The information you want to get from the database

• How you want to use that information

When deciding how to structure database tables, involve future users of the database as well as the people who will make decisions based on database information. If you come up with what you think is a reasonable structure, but it isn’t consistent with the way that people will use the information, your system will be frustrating to use at best — and could even produce wrong information, which is even worse.

Take a look at an example. Suppose you just established VetLab, a clinical microbiology laboratory that tests biological specimens sent in by veterinarians. You want to track several things, including the following:

• Clients

• Tests that you perform

• Employees

• Orders

• Results

Each of these entities has associated attributes. Each client has a name, an address, and other contact information. Each test has a name and a standard charge. Each employee has contact information as well as a job classification and pay rate. For each order, you need to know who ordered it, when it was ordered, and what test was ordered. For each test result, you need to know the outcome of the test, whether the results were preliminary or final, and the test order number.

Step 3: Define tables

Now you want to define a table for each entity and a column for each attribute. You can create the tables defined here by using either a rapid application development (RAD) tool or by using SQL’s Data Definition Language (DDL), as shown in the following code:

CREATE TABLE CLIENT (
  ClientName  CHAR (30)  NOT NULL,
  Address1   CHAR (30),
  Address2   CHAR (30),
  City    CHAR (25),
  State    CHAR (2),
  PostalCode  CHAR (10),
  Phone    CHAR (13),
  Fax    CHAR (13),
  ContactPerson  CHAR (30) ) ;
CREATE TABLE TESTS (
  TestName   CHAR (30)  NOT NULL,
  StandardCharge CHAR (30) ) ;
CREATE TABLE EMPLOYEE (
  EmployeeName  CHAR (30)  NOT NULL,
  Address1   CHAR (30),
  Address2   CHAR (30),
  City    CHAR (25),
  State    CHAR (2),
  PostalCode  CHAR (10),
  HomePhone   CHAR (13),
  OfficeExtension CHAR (4),
  HireDate   DATE,
  JobClassification CHAR (10),
  HourSalComm  CHAR (1) ) ;
CREATE TABLE ORDERS (
  OrderNumber  INTEGER   NOT NULL,
  ClientName  CHAR (30),
  TestOrdered  CHAR (30),
  Salesperson  CHAR (30),
  OrderDate   DATE ) ;
CREATE TABLE RESULTS (
  ResultNumber  INTEGER   NOT NULL,
  OrderNumber  INTEGER,
  Result   CHAR(50),
  DateReported  DATE,
  PrelimFinal  CHAR (1) ) ;

These tables relate to each other by the attributes (columns) that they share, as the following list describes:

• The CLIENT table links to the ORDERS table by the ClientName column.

• The TESTS table links to the ORDERS table by the TestName (TestOrdered) column.

• The EMPLOYEE table links to the ORDERS table by the EmployeeName (Salesperson) column.

• The RESULTS table links to the ORDERS table by the OrderNumber column.

If you want a table to serve as an integral part of a relational database, link that table to at least one other table in the database, using a common column.

The links illustrate four different one-to-many relationships. The diamond in the middle of each relationship shows the maximum cardinality of each end of the relationship. The number 1 denotes the “one” side of the relationship, and N denotes the “many” side.

• One client can make many orders, but each order is made by one, and only one, client.

• Each test can appear on many orders, but each order calls for one, and only one, test.

• Each order is taken by one, and only one, employee (or salesperson), but each salesperson can take multiple orders.

• Each order can produce several preliminary test results and a final result, but each result is associated with one, and only one, order.

The attribute that links one table to another can have a different name in each table. Both attributes must, however, have matching data types.