SQL Tutorial: Normalizing a Database

To "normalize" a database, or to split things up into unique and manageable chunks, I'll create the following (switching to a store-front example this time):

If in this example I were continuing the first example, I might have chosen to create (a) a table for stock purchase information, (b) a table for current prices, (c) a view combining pieces of each to show the captial gain, and any number of other items.

ericj=> CREATE TABLE sales_customer (
ericj(>   id         varchar(8),
ericj(>   name       varchar(30),
ericj(>   str_addr1  varchar(30),
ericj(>   str_addr2  varchar(30),
ericj(>   city       varchar(15),
ericj(>   state      varchar(2),
ericj(>   zip_4      varchar(10),
ericj(>   country    varchar(10)
ericj(> );
...
ericj=> CREATE TABLE sales_item (
ericj(>   id           varchar(8),
ericj(>   name         varchar(30),
ericj(>   description  varchar(255),
ericj(>   price        float,
ericj(>   imagetag     varchar(255)
ericj(> );
...
ericj=> CREATE TABLE sales_purch (
ericj(>   id          int,
ericj(>   purch_date  date,
ericj(>   cust_id     varchar(8),
ericj(>   item_id     varchar(8),
ericj(>   quantity    int
ericj(> );
...
ericj=> CREATE VIEW sales_invoice AS (
ericj(>   SELECT sales_customer.name,
ericj(>     sales_purch.purch_date,
ericj(>     sales_item.name AS item_name,
ericj(>     sales_item.price,
ericj(>     sales_purch.quantity,
ericj(>     (sales_purch.quantity * sales_item.price) AS subtotal
ericj(>   FROM sales_customer, sales_item, sales_purch
ericj(>   WHERE (sales_customer.id = sales_purch.cust_id) AND
ericj(>         (sales_item.id = sales_purch.item_id)
ericj(> );
...
ericj=>

Notice how the VIEW fields have now changed from a simpler "fieldname" reference to a "tablename"."fieldname" reference instead (you can usually leave out the quotes as I did this time, but if you keep them, that order is important -- it is "tablename"."fieldname", not "tablename.fieldname"). When a fieldname is unique, this is not required, but in this example, all tables have their own "id" field, and PostgreSQL needs to know which "id" I meant.

Then for readability, it pays to be consistent. The same concept goes for not needing capital letters for the commands. There's no syntax difference between "SELECT * FROM sales_item;" and "select * from sales_item;", but on the longer lines it helps to see at a glance which are my names and which are PostgreSQL terms.

To enter the HTML image tag in a field, the text for that field should simply be the usual HTML tag you'd use in any web page. This way, the image itself isn't stored in the database, but rather the URL is, which will save space in the long run if your own database has scores or hundreds of entries. There's a bit of a trick in showing the images that way from a CGI script, and I'll delve into that topic in the next section.

SQL Tutorial II

Normalizing a Database