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
--
Psssst! Schon vom neuen GMX MultiMessenger gehört?
Der kann`s mit allen: http://www.gmx.net/de/go/multimessenger
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
