Step One: Build the Tables

There are five tables that need to be built. The customer table needs a column for first name, last name, address, city, state, and phone at a minimum. It should also have an ID column so that it can be easily associated with other tables. I would like to make most of the information optional, to allow maximum flexibility in adding a customer, so only last name will be a required field. Here is a first crack at customers:

 create table customer(
     cust_id int identity(1,1),
     fname varchar(30) null,
     lname varchar(30) not null,
     addr varchar(50) null,
     city varchar(30) null,
     state char(2) null,
     phone char(10) null
     )

The ID field’s identity property ensures that a unique, ascending number will be inserted for each new customer. This will act as the primary key for this table. Because it is an integer, there are over 2 billion potential customer rows—more than enough for my widgets to thrive. Now, I’ll rewrite the table definition to include the PK constraint:

create table customer(
    cust_id int identity(1,1)
        constraint PK_NC_cust_id primary key nonclustered,
    fname varchar(30) null,
    lname varchar(30) not null,
    addr varchar(50) null,
    city varchar(30) null,
    state char(2) null,
    phone char(10) null
    )

All constraints in my tables will be named. This primary key constraint builds an index, and I chose to build a nonclustered type.

---

Technical Note:  Clustered indexes and identity columns
If I were really building these tables, after they were built, I would also add indexes to speed their operation. Clustered indexes physically order the data in the table. If there are lots of people inserting to the table at the same time, I would want a clustered index on name, to distribute the “hot spots.” Usually, you should not build clustered indexes on identity columns, because deleted rows will never have their space reused.

The topic of SQL Server index types and usage has been thoroughly explored in other works, and I don’t want to get into it heavily here. The short version of this is: “Don’t define clustered indexes on identity columns.”

Foreign keys and indexes

The server builds an index when you define a primary key, but not when you define a foreign key. Most of the time, to enhance performance, foreign key columns should have an index built on them. When tables are joined, the indexes speed up performance dramatically.

---

The name I chose for this constraint includes the letters PK to indicate that it is a primary key, the letters NC to indicate it is a nonclustered index, and the column on which the constraint is defined.

The item table can’t be as flexible as the customer table and won’t contain any null columns. I usually stock new widgets in blocks of 100, so I will default the qty column to 100. I will never have more than a thousand or two thousand widgets, so I will use a small integer (up to 32,768) for the ID column. The item_id column will act as the primary key for this table. The item table does not reference other tables; it has no foreign keys.

I could have placed the primary key constraint on the descr column, but that would make the relationships between other tables very difficult. In order to reference an item in the inv_detail table, this definition allows the detail table to have item numbers, instead of descriptions. However, no two items should have the same description, so I’ll place a unique constraint on the descr column.

---

Note:  The descr column cannot be named “desc” because that is a reserved word, meaning “descending.” It is usually a bad idea to use reserved words in creating tables, although you are allowed to do it. To use reserved words, first set quoted_identifier on by issuing the command

set quoted_identifier on

You then can submit object names that are reserved words only in double quotes:

create table “table” (c1 “integer” int not null)

---

create table item(
    item_id smallint identity
        constraint PK_NC_item_id primary key nonclustered,
    descr varchar(80) not null
        constraint UQ_NC_desc unique,
    price smallmoney not null,
    qty smallint not null default 100
    )

The invoices table should be relatively flexible in allowing entries. This table is the first to contain a potential foreign key. It is not required that the related columns have the same name, but there is no compelling reason not to name related columns differently. It is definitely helpful to name them the same. Invoices will contain a cust_id column that will reference customer.cust_id. It will also have a primary key column, inv_id, to uniquely identify each invoice. Note that the cust_id data type of integer is matched to the customer table’s data type. This is required to create a DRI reference constraint.

create table invoices(
    inv_id int identity constraint PK_NC_inv_id
        primary key nonclustered,
    cust_id int not null
        constraint FK_invoices_cust_id
        foreign key references customer(cust_id),
    saledate datetime null,
    amt smallmoney null
    )

The saledate and amt columns were left as null columns to allow a salesperson to enter an invoice, list information in the invoice detail table, and leave dates and amounts blank until later.

The last table to create is the invoice detail table. This detail table lists items that were sold on a particular invoice. The detail table is separate from the invoice table because there is information in invoices—saledate and customer ID—that does not need to be repeated for each item.