Creating Joined Tables

Figure  shows the SQL statements needed to create the tables in our mail-order example. ^10.1  

        test=> CREATE TABLE customer (                              
        test(>                        customer_id INTEGER,
        test(>                        name        CHAR(30),
        test(>                        telephone   CHAR(20),
        test(>                        street      CHAR(40),
        test(>                        city        CHAR(25),
        test(>                        state       CHAR(2),
        test(>                        zipcode     CHAR(10),
        test(>                        country     CHAR(20)
        test(> );
        CREATE
        test=> CREATE TABLE employee (
        test(>                        employee_id INTEGER,
        test(>                        name        CHAR(30),
        test(>                        hire_date   DATE
        test(> );
        CREATE
        test=> CREATE TABLE part (
        test(>                    part_id     INTEGER,
        test(>                    name        CHAR(30),
        test(>                    cost        NUMERIC(8,2),
        test(>                    weight      FLOAT
        test(> );
        CREATE
        test=> CREATE TABLE salesorder (
        test(>                 order_id      INTEGER,
        test(>                 customer_id   INTEGER,  -- joins to customer.customer_id
        test(>                 employee_id   INTEGER,  -- joins to employee.employee_id
        test(>                 part_id       INTEGER,  -- joins to part.part_id
        test(>                 order_date    DATE,
        test(>                 ship_date     DATE, 
        test(>                 payment       NUMERIC(8,2)
        test(> );
        CREATE  

The customer, employee, and part tables all have a column to hold their unique identification numbers. The salesorder ^10.2 table includes columns to hold the customer, employee, and part numbers associated with a particular sales order. For the sake of simplicity, we will assume that each salesorder entry contains only one part number.

  We have used underscore (_) to allow the use of multiple words in column names--for example, customer_id. This is a common practice. You could enter the column as CustomerId, but POSTGRESQL converts all identifiers, such as column and table names, to lowercase; thus the actual column name becomes customerid, which is not very clear. The only way to define nonlowercase column and table names is to use double quotes. Double quotes preserve any capitalization you supply. You can even have spaces in table and column names if you surround the name with double quotes (")--for example, "customer id". If you decide to use this feature, you must put double quotes around the table or column name every time it is referenced. Obviously, this practice can be cumbersome.

Keep in mind that all table and column names not protected by double quotes should consist of only letters, numbers, and the underscore character. Each name must start with a letter, not a number. Do not use punctuation, except the underscore, in your names. For example, address, office, and zipcode9 are valid names, but 2pair and my# are not.  

The example in Figure  also shows the existence of a column named customer_id in two tables. This duplication occurs because the two columns contain the same type of number, a customer identification number. Giving them the same name clearly shows which columns join the tables together. If you wanted to use unique names, you could name the column salesorder_customer_id or sales_cust_id. This choice makes the column names unique, but still documents the columns to be joined.

Figure  shows the insertion of a row into the customer, employee, and part tables. It also shows the insertion of a row into the salesorder table, using the same customer, employee, and part numbers to link the salesorder row to the other rows we inserted.  

        test=> INSERT INTO customer VALUES (
        test(>                              648,
        test(>                              'Fleer Gearworks, Inc.',
        test(>                              '1-610-555-7829',
        test(>                              '830 Winding Way',
        test(>                              'Millersville',
        test(>                              'AL',
        test(>                              '35041',
        test(>                              'USA'
        test(> );
        INSERT 19815 1
        test=> INSERT INTO employee VALUES (
        test(>                              24,
        test(>                              'Lee Meyers',
        test(>                              '10/16/1989'
        test(> );
        INSERT 19816 1
        test=> INSERT INTO part VALUES ( 
        test(>                           153,
        test(>                           'Garage Door Spring',
        test(>                           6.20
        test(> );
        INSERT 19817 1
        test=> INSERT INTO salesorder VALUES( 
        test(>                               14673,
        test(>                               648,
        test(>                               24, 
        test(>                               153,
        test(>                               '7/19/1994',
        test(>                               '7/28/1994',
        test(>                               18.39
        test(> );
        INSERT 19818 1  

For simplicity, we will use only a single row per table.