Re: INSERT INTO relational tables - Mailing list pgsql-sql

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
>find any information about (google, forums). By
>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
>about the customer (name, address, ...) and
>about his order (pieces, product-name, price).
>Because one customer can order several products
>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,
>ord_price, ord_customer REFERENCES tbl_customer(cust_id))
>
>Now I want to insert several new customers with
>several orders each at once. If I had all
>information in one table, this would be easy with something like:
>
>INSERT INTO tbl_customerorders (name, address,
>pieces, porductname, price) VALUES ('MR. X',
>'1st street', 3, 't-shirts', 30), ('MR. X', '1st
>street', 5, 'books', 50),  ('MRS. Y', '2nd
>street', 1, 't-shirt', 10),...
>
>But how can I do this in one query if I split
>the table? I can add one new customer, get his
>ID with curval() and then add his orders. But
>this won’t work if I want to add several customers at once.
>
>To read this information I can do a query with
>the argument WHERE cust_id=ord_customer. I can
>create a VIEW doing this so I can read the data
>as if it was stored in only one table. But is
>there in posgres/sql an abstraction-layer that
>allows me to insert as if the information was
>stored in one table? (Something like a VIEW that
>provides INSERT, UPDATE, … and automatically
>inserts the referenced ID.)
>
>Thanks for any help!
>Stefan

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

Perhaps all you need is to wrap your entire set
of statements into a transaction
(http://www.postgresql.org/docs/8.2/interactive/tutorial-transactions.html)
to ensure that your entires are "acid"
(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
simplicity or referential integrity (or both). If
syntax is your need, then Andreas has given some good ideas.

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

I hope this is helpful,

Steve