Re: Newbie table definition question - Mailing list pgsql-general

* Ken Tozier <kentozier@comcast.net> [2004-10-16 13:50:37 -0400]:

> I'm a C/Objective C programmer and am having a bit of difficulty
> figuring out how to define SQL table approximations to things that
> are very easy to do in C/Objective C

I sympathize; no matter how many languages you know, there's always a
learning curve involved trying to pick up the nuances of the next one.

> Basically what I'm confused about is how to simulate arrays of
> structs in Postgres. For example, if I define a C struct like so

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
);

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
);

ALTER TABLE cart ADD CONSTRAINT grocery_items_id_exists FOREIGN KEY (grocery_items_id) REFERENCES grocery_items(id);
ALTER TABLE grocery_items ADD CONSTRAINT grocery_types_id_exists FOREIGN KEY (grocery_types_id) REFERENCES
grocery_types(id);

INSERT INTO grocery_types (name) VALUES ('fruit');
INSERT INTO grocery_types (name) VALUES ('vegatable');

INSERT INTO grocery_items (grocery_types_id, name, price) VALUES (1, 'Apple', '0.50');
INSERT INTO grocery_items (grocery_types_id, name, price) VALUES (1, 'Orange', '0.75');

INSERT INTO grocery_items (grocery_types_id, name, price) VALUES (1, 'Brocolli', '1.35');
INSERT INTO grocery_items (grocery_types_id, name, price) VALUES (1, 'Lettuce', '2.55');

HTH,

--
Steven Klassen - Lead Programmer
Command Prompt, Inc. - http://www.commandprompt.com/
PostgreSQL Replication & Support Services, (503) 667-4564