How to Design a 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:
Decide what objects you want to include in your database.
Determine which of these objects should be tables and which should be columns within those tables.
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:
Tests that you perform
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.