Re: Newbie table definition question

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 <kentozier@comcast.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
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faqs/FAQ.html
>