Thanks again Stephen
It helps to see a problem you understand defined in a language you don't. I've got a handhold now.
Ken
On Oct 17, 2004, at 4:52 AM, Steven Klassen wrote:
* Ken Tozier <[EMAIL PROTECTED]> [2004-10-17 00:25:07 -0400]:
So, assuming there's no "quantity" field defined in the "cart" table, if 3 apples, 2 oranges and 1 head of lettuce were purchased on a specific shopping trip, I would do something like this?
If you need a quantity field, add one.
CREATE TABLE cart ( id bigserial primary key NOT NULL, trips_id bigint NOT NULL, grocery_items_id bigint NOT NULL, quantity integer NOT NULL -- assuming whole numbers );
INSERT INTO cart (trips_id, grocery_items_id, quantity) VALUES ({1, 1, 3}, {1, 2, 2}, {1, 4, 1})
Separate queries, but that's the idea.
Having to remember ids for grocery items seem rather user-unfriendly. Would this be a candidate for a view? Allowing the user to enter something like {{"apples", 3}, {"oranges", 2}, {"lettuce", 1}}
The idea is that whatever interface you use will be able to use the numeric values in the grocery_types table.
For example, you'd pull the id/name pairs from grocery_types and generate a drop-down box for them to select:
<select name="grocery_types_id"> <option value="1">Fruit</option> <option value="2">Vegetable</option> </select>
Now if you needed to get at all the items you've defined along with the names of the types in a human-readable format, you could create a view like the following:
CREATE VIEW items_types_view AS
SELECT grocery_items.id,
grocery_items.name AS item_name,
grocery_types.name AS type_name
FROM grocery_items
JOIN grocery_types ON (grocery_items.grocery_types_id = grocery_types.id);
xinu=> select * from items_types_view; id | item_name | type_name ----+-----------+----------- 1 | Apple | fruit 2 | Orange | fruit 3 | Brocolli | fruit 4 | Lettuce | fruit (4 rows)
The documentation on the postgreSQL site is going to be your best bet for up-to-date information, but the Practical PostgreSQL book is still an easy read for the basics.
http://www.commandprompt.com/ppbook/
-- Steven Klassen - Lead Programmer Command Prompt, Inc. - http://www.commandprompt.com/ PostgreSQL Replication & Support Services, (503) 667-4564
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
