Re: Help with the big picture

inline...

----- Original Message -----
From: "Brad Paul" <bpaul(at)carolina(dot)rr(dot)com>
To: <pgsql-novice(at)postgresql(dot)org>
Sent: Monday, October 14, 2002 11:46 AM
Subject: [NOVICE] Help with the big picture

> 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?

Currently we use access (less and less, due to me :) to update our Mysql
database tables. (we're migrating to postgresql). Our biggest problem is
access is too "smart". You have to ensure that you DON'T have autocorrect
turned on. You also have to ensure that your forms scrub for data that
access would otherwise permit eg: hard returns. Just by hitting CTRL-Enter
in text box, you can insert a hard return into your field... which is evil,
I think. Also, ensure that the form items that you don't want edited ARE
LOCKED from doing so. With access's tabbing through fields, we've had user
deletions of field data inadvertantly. Also, Access's filter function has
been a problem for us, where users filter to find a record, and then when
they update it, they don't turn the filter off, and the filter'd values are
then instantiated into the data fields inadvertantly. I don't know if this
makes sense, it's early in the morning.

> 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_cod
e)
> select
>
>
new.address_1,new.address_2,new.address_3,new.city,new.state_provence,new.co
untry,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_prove
nce,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_prove
nce,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_prove
nce,country,postal_code)
> values
> ('Mr','Ted','Smith','300','10 main','The Pas','MB','Canada','2d3
3e3');
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>