Message-ID: <43A03555.6050007@moonwatcher.it>
Date: Wed, 14 Dec 2005 16:08:05 +0100
From: Gianluca Riccardi <ml-reader@moonwatcher.it>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; it-IT;
	rv:1.7.8) Gecko/20050927 Debian/1.7.8-1sarge3
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Re: DB design and foreign keys
References: <439F0FE8.1050503@moonwatcher.it>
In-Reply-To: <439F0FE8.1050503@moonwatcher.it>
Content-Type: text/plain; charset=ISO-8859-15; format=flowed
Content-Transfer-Encoding: 8bit

so, after the needed modifications the SQL schema is the following

-- SQL schema for business-test-db2

CREATE TABLE customers (
   customer_code serial UNIQUE,
   alfa_customer_code varchar(6),
   customer_name character varying(250) NOT NULL,
   address character varying(250) NOT NULL,
   city character varying(250) NOT NULL,
   zip_code character varying(8) NOT NULL,
   prov character varying(30) NOT NULL,
   security character varying(15) NOT NULL,
   tel character varying(30),
   tel2 character varying(20) NOT NULL,
   fax character varying(250),
   url character varying(250),
   email1 character varying(250) NOT NULL,
   email2 character varying(250) NOT NULL,
   discount1 integer,
   discount2 integer,
   PRIMARY KEY (customer_code)
);

CREATE TABLE users  (
   id smallint NOT NULL,
   login varchar(20) NOT NULL,
   pwd varchar(20) NOT NULL,
   name varchar(20) NOT NULL,
   customer_code int REFERENCES customers (customer_code),
   valid date,
   primary key (id)
);

CREATE TABLE products   (
   code varchar(60) UNIQUE NOT NULL,
   description varchar(250) NOT NULL,
   dimensions varchar(250) NOT NULL,
   price numeric NOT NULL,
   state boolean,
   PRIMARY KEY (code)
);

CREATE TABLE orders  (
   order_code serial UNIQUE NOT NULL,
   customer_code integer REFERENCES customers (customer_code) NOT NULL,
   order_date time without time zone NOT NULL,
   remote_ip inet NOT NULL,
   order_time timestamp with time zone NOT NULL,
   order_type varchar(10) NOT NULL,
   state varchar(10) NOT NULL,
   PRIMARY KEY (order_code)
);

CREATE TABLE order_items (
   order_code integer REFERENCES orders (order_code) NOT NULL,
   customer_code integer REFERENCES customers (customer_code) NOT NULL,
   product_code varchar(60) REFERENCES products (code) NOT NULL,
   qty int NOT NULL,
   price numeric NOT NULL,
   row_price numeric
);

--
-- END OF FILE

the order_items table is:

business-test-db2=# \d order_items
             Tabella "public.order_items"
    Colonna         |         Tipo                 | Modificatori
-------------------+-------------------------+--------------
 order_code       | integer                      | not null
 customer_code | integer                      | not null
 product_code   | character varying(60) | not null
 qty                  | integer                      | not null
 price                | numeric                    | not null
 row_price        | numeric                    |
Vincoli di integrità referenziale
    "$1" FOREIGN KEY (order_code) REFERENCES orders(order_code)
    "$2" FOREIGN KEY (customer_code) REFERENCES customers(customer_code)
    "$3" FOREIGN KEY (product_code) REFERENCES products(code)

business-test-db2=#


thanks a lot, you all pointed me out of misconceptualized position.

best ragards all,
Gianluca Riccardi

p.s.

Colonna = column
Tipo = type
Modificatori = modifiers
Vincoli di integrita' referenziale = referential integrity constraints