> 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
