Need help with the big picture. - Mailing list pgsql-novice

I'm very new to data bases (last week was day one). My goal is to put
our data base in PostgreSQL on a Linux computer and have MS Access be
the front end for taking orders etc. (I never used Access ether but
our new office manager seems quit good at it.)

Before I jump in and try to build our real data base I thought I would
try something rather simple. I have three tables, an address table, an
employee table and a customer table. The employee table has a foreign key
that points back to the address table and the customer table has two
foreign keys that point back to the address table one for shipping and
on for billing.

I have made a view for viewing the employee data with address
information and a rule that allows me to insert data into the view. I
have gotten this to work from the command line in psql. However I can
not insert data into the view with MS Access. I can insert data into
the real tables with MS Access.

I have many questions:

1) Should I not use views and try to only update tables with Access? I
have tried this but then I was unable to get the links in Access to do
what they should. I should not have to manually find the new address_id
to place it in the employee table when adding a new employee. (However, if
the x-mass season does not go well we will never have a real example
of this, but it would be a good thing to know for my next job.)

2) How do I get Access to be able to add data to a view, that has a
rule that works in psql?

3) Am I missing something big?

4) How do I deal with removing employees? (A more realistic event.) I
will need to check if the employees address is referenced by any other
table. (i.e. maybe the employees wife is also a customer and they live
together.) Should I try and make rules to do this or learn how to write
functions?

For you entertainment I have included my current data base set up:


-- The drops
drop rule employee_view_insert;

drop view employee_view;

drop  sequence employee_id_seq;
drop  sequence address_id_seq;
drop  sequence customer_id_seq;

drop table customer;
drop table employee;
drop table address;

-- The code
create sequence "address_id_seq" start 1 increment 1;

Create table "address" (
    "address_id" int4 default nextval('address_id_seq')  NOT NULL unique,
    "address_1" character varying(50) not null,
    "address_2" character varying(50),
    "address_3" character varying(50),
    "city"    character varying(50) not null,
    "state_provence"    character varying(50),
    "country" character varying(50),
    "postal_code" character varying(20),
    primary key ("address_1","city","state_provence")
);

create sequence "employee_id_seq" start 1 increment 1;
create table "employee" (
       "employee_id" int4 default nextval('employee_id_seq') NOT NULL unique,
       "prefix" character varying(8),
       "first_name" character varying(50) not null,
       "last_name" character varying(50) not null,
       "address_id"  int4  NOT NULL references address (address_id),
       "saliery" numeric(9,2),
       primary key ("first_name","last_name")
);


create sequence "customer_id_seq" start 1 increment 1;
create table "customer" (
       "customer_id" int4 default nextval('customer_id_seq') NOT NULL unique,
       "prefix" character varying(8),
       "first_name" character varying(50) not null,
       "last_name" character varying(50) not null,
       "ship_address_id"  int4   references address (address_id),
       "bill_address_id"  int4   references address (address_id),
       primary key ("first_name","last_name")
);


create view employee_view as
       select employee.prefix as prefix , employee.first_name as first_name,
employee.last_name as last_name , employee.saliery as saliery,
          address.address_1 as address_1, address.address_2 as
address_2,address.address_3 as address_3,
          address.city as city ,address.state_provence as
state_provence,address.country as country,address.postal_code as postal_code
       from employee,address
       where employee.address_id = address.address_id;


create rule employee_view_insert as
       on insert to employee_view
       do instead
       (
       insert into
address(address_1,address_2,address_3,city,state_provence,country,postal_code)
       select

new.address_1,new.address_2,new.address_3,new.city,new.state_provence,new.country,new.postal_code
       where not exists
         (select address.address_id from address where address.address_1 =
new.address_1
         and address.city=new.city and address.state_provence =
new.state_provence);

       insert into employee(prefix,first_name,last_name,address_id,saliery)
       select
new.prefix,new.first_name,new.last_name,address.address_id,new.saliery from
address
       where new.address_1=address.address_1 and new.city=address.city and
new.state_provence = address.state_provence;
       );


insert into address(address_1,city,state_provence,country,postal_code)
       values
       ('1011 Farmingham Ln','Indina Trail','North Carolina','USA','28079');
insert into address(address_1,city,state_provence,country,postal_code)
       values
       ('123 Somerset','Delaware','Ohio','USA','43015');
insert into address(address_1,city,state_provence,country,postal_code)
       values
       ('444 Mast Rd','Gun Barrel','Colorado','USA','80102');

insert into employee(prefix,first_name,last_name,address_id,saliery)
       values
       ('Mr','Brad','Paul','1','2.00');
insert into employee(prefix,first_name,last_name,address_id,saliery)
       values
       ('Mr','Dave','Paul','2','2.00');

insert into
customer(prefix,first_name,last_name,ship_address_id,bill_address_id)
       values
       ('Mr','Brad','Paul','1','1');
insert into
customer(prefix,first_name,last_name,ship_address_id,bill_address_id)
       values
       ('Ms','Myrna','Paul','2','1');
insert into
customer(prefix,first_name,last_name,ship_address_id,bill_address_id)
       values
       ('Mr','Hans','Green','3','3');

-- 1+1=2 so I can see which one works etc.
select 1+1;
INSERT INTO
employee_view(prefix,first_name,last_name,saliery,address_1,city,state_provence,country,postal_code)
       values
       ('ms','Abigail','Adams','500','11 main','Qunicy','MA','USA','01234');
select 1+1;
INSERT INTO
employee_view(prefix,first_name,last_name,saliery,address_1,city,state_provence,country,postal_code)
       values
       ('Mr','ed','smith','300','10 main','The Pas','MB','Canada','2d3 3e3');
select 1+1;
INSERT INTO
employee_view(prefix,first_name,last_name,saliery,address_1,city,state_provence,country,postal_code)
       values
      ('Mr','Ted','Smith','300','10 main','The Pas','MB','Canada','2d3 3e3');