Thread: 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?
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');
Brad Paul, > 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.) Please see Techdocs: http://techdocs.postgresql.org for lots of helpful advice. Particularly, there is a Book Review page with several good reccomendations for database and PostgreSQL books. For example, "Database Design for Mere Mortals". > 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. This may be a limitation of MS Access, or of ODBC on Windows. I know that I cannot use many kinds of defaults when using MS Access as a front end; Access seems to insist on inserting a NULL. There are two mailing lists that might help you: pgsql-odbc@postgresql.org and for http://pgadmin.postgresql.org > 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.) Well, if you're looking for "shoulds", I'd say the first "should" is "should not use MS Access as an interface." I spent 5 years developing applications based on MS Access and Visual Basic; they are all high-maintainence and buggy as all-get-out, except for the smallest, simplest applications. MS Access is just an unstable, quirky platform. Mind you, some of these applications are still paying me because they require weekly support just to keep running ... I realize that you probably don't have a choice. Just wanted to let you know -- it's not you, it's the software. You could try Borland's Delphi suite, or one of the advanced Java IDEs. Sadly, Postgres' accompanying inteface developement kit, PGAccess, is not quite ready for you to build an application out of it. You should tinker with it a bit and try, though, it might be better than Access: http://www.pgaccess.org/ Or you could learn Zope and build and intranet-based application. I'm told that Zope is impressively developer friendly. That being said, if Access is preventing you from updating the views, you would have to learn a little Visual Basic and write some code attached to the employees form to update both tables. > 2) How do I get Access to be able to add data to a view, that has a > rule that works in psql? Petition Microsoft to fix their buggy software? <grin> good luck. Work around it, or post your questions on the lists I mention above. > 3) Am I missing something big? No. What you want to do *is* the better way to do things ... it's just that your software (MS Access) doesn't support it. > 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? Writing rules and functions is very similar. It shouldn't be hard. I suggest that you write a "before" trigger for the employees table. It may seem daunting at first, but you can easily get through it with some help from books. -Josh Berkus
inline...
----- Original Message -----
From: "Brad Paul" <bpaul@carolina.rr.com>
To: <pgsql-novice@postgresql.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
>