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:-
Clients
-
Tests that you perform
-
Employees
-
Orders
-
Results
Step 3: Define tables
Now you want to define a table for each entity and a column for each attribute.Table | Columns |
---|---|
CLIENT | Client Name |
Address 1 | |
Address 2 | |
City | |
State | |
Postal Code | |
Phone | |
Fax | |
Contact Person | |
TESTS | Test Name |
Standard Charge | |
EMPLOYEE | Employee Name |
Address 1 | |
Address 2 | |
City | |
State | |
Postal Code | |
Home Phone | |
Office Extension | |
Hire Date | |
Job Classification | |
Hourly/Salary/Commission | |
ORDERS | Order Number |
Client Name | |
Test Ordered | |
Responsible Salesperson | |
Order Date | |
RESULTS | Result Number |
Order Number | |
Result | |
Date Reported | |
Preliminary/Final |
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.
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.