Re: [GENERAL] Newbie table definition question

[Ken Tozier]

You can track whatever information you need about the particular trip,
add rows to the cart associating the trip with the items being
purchased, and finally the grocery types and items.
CREATE TABLE trips (
    id bigserial primary key NOT NULL,
    created timestamp default now() NOT NULL
);
CREATE TABLE cart (
    id bigserial primary key NOT NULL,
    trips_id bigint NOT NULL,
    grocery_items_id bigint NOT NULL,
       quantity int NOT NULL
);
CREATE TABLE grocery_types (
    id bigserial primary key NOT NULL,
    name text NOT NULL
);
CREATE TABLE grocery_items (
    id bigserial primary key NOT NULL,
    grocery_types_id bigint NOT NULL,
    name text NOT NULL,
    price numeric(10,2) NOT NULL,
);

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?

INSERT INTO cart (trips_id, grocery_items_id) VALUES ({1, 1}, {1, 1}, 
{1, 1}, {1, 2}, {1, 2}, {1, 4})

With a "quantity" field the same shopping trip would look like this:
INSERT INTO cart (trips_id, grocery_items_id, quantity) VALUES ({1, 1, 
3}, {1, 2, 2}, {1, 4, 1})

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}}

Ken
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
              http://archives.postgresql.org