Date: Sat, 08 Dec 2007 23:50:23 -0800
To: pgsql-sql-owner@postgresql.org,pgsql-sql@postgresql.org
From: Steve Midgley <public@misuse.org>
Subject: Re: INSERT INTO relational tables
In-Reply-To: <20071207172528.820B92C96D4@maia-2.hub.org>
References: <20071207172528.820B92C96D4@maia-2.hub.org>
Mime-Version: 1.0
Content-Type: text/plain; charset="iso-8859-1"; format=flowed
Content-Transfer-Encoding: quoted-printable
Message-Id: <20071209075032.2A1112E3294@postgresql.org>

At 09:23 AM 12/7/2007, pgsql-sql-owner@postgresql.org wrote:
>Date: Fri, 07 Dec 2007 14:22:26 +0100
>From: "Stefan Scheidegger" <Stefan.Scheidegger@gmx.net>
>To: pgsql-sql@postgresql.org
>Subject: INSERT INTO relational tables
>Message-ID: <20071207132226.281710@gmx.net>
>
>Hi all
>
>I'm new to SQL and I'm facing a problem I can't=20
>find any information about (google, forums). By=20
>now I wonder if I understood something wrong about relational DBs.
>
>An example to explain my Problem:
>Lets say I have a table containing information=20
>about the customer (name, address, ...) and=20
>about his order (pieces, product-name, price).=20
>Because one customer can order several products=20
>I split the table into two relational tables to prevent redundancy:
>
>tbl_customer (cust_id, cust_name, cust_address)
>and
>tbl_order (ord_pieces, ord_productname,=20
>ord_price, ord_customer REFERENCES tbl_customer(cust_id))
>
>Now I want to insert several new customers with=20
>several orders each at once. If I had all=20
>information in one table, this would be easy with something like:
>
>INSERT INTO tbl_customerorders (name, address,=20
>pieces, porductname, price) VALUES ('MR. X',=20
>'1st street', 3, 't-shirts', 30), ('MR. X', '1st=20
>street', 5, 'books', 50),  ('MRS. Y', '2nd=20
>street', 1, 't-shirt', 10),...
>
>But how can I do this in one query if I split=20
>the table? I can add one new customer, get his=20
>ID with curval() and then add his orders. But=20
>this won=E2=80=99t work if I want to add several customers at once.
>
>To read this information I can do a query with=20
>the argument WHERE cust_id=3Dord_customer. I can=20
>create a VIEW doing this so I can read the data=20
>as if it was stored in only one table. But is=20
>there in posgres/sql an abstraction-layer that=20
>allows me to insert as if the information was=20
>stored in one table? (Something like a VIEW that=20
>provides INSERT, UPDATE, =E2=80=A6 and automatically=20
>inserts the referenced ID.)
>
>Thanks for any help!
>Stefan

Andrea has given you some "deep" answers (i.e.=20
smarter than what I'm going to say) but since you=20
say you're a newbie to SQL maybe this will help.

Perhaps all you need is to wrap your entire set=20
of statements into a transaction=20
(http://www.postgresql.org/docs/8.2/interactive/tutorial-transactions.html)=
=20
to ensure that your entires are "acid"=20
(http://databases.about.com/od/specificproducts/a/acid.htm).

BEGIN
insert tbl_customer...;
insert tbl_order...;
insert tbl_order...;
END

It's not clear whether you're looking for syntax=20
simplicity or referential integrity (or both). If=20
syntax is your need, then Andreas has given some good ideas.

As another thought about syntax enhancement:=20
there are lots of object-relational mappers out=20
there that let you model your relations in a=20
higher level language (I use Ruby and=20
ActiveRecord which are easy to write and learn,=20
but Java, .NET, etc all have great stuff). Then=20
you just build your "entities" in the domain=20
specific language and it handles all the id=20
inserts and relational mapping for you. They can=20
even handle mapping many-to-many joined entities,=20
if you're careful in setting it up.

I hope this is helpful,

Steve