Re: Newbie table definition question

* Ken Tozier <kentozier(at)comcast(dot)net> [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