Thread: Newbie table definition question
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
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
typedef struct Fruit
{
char name[32];
float price;
}Fruit;
typedef struct Veggies
{
char name[32];
float price;
} Veggies;
typedef struct GroceryBill
{
unsigned long date;
long veggieCount;
Veggies *veggies;
long fruitCount;
Fruit *fruits;
} GroceryBill;
The nice thing in C is that I can assign an arbitrary array of
identically structured "fruits" or "veggies" to a single field in a
parent data structure. Is this possible in SQL?
My best guess would be to do something like this:
CREATE TABLE veggies (
name varchar(32) primary key,
price real
);
CREATE TABLE fruit (
name varchar(32) primary key,
price real
);
CREATE TABLE groceries (
date date;
veggies varchar(32) references veggies,
fruit varchar(32) references fruit,
);
But it seems like the "veggies" and "fruit" fields within the
"groceries" table would only reference a single entry in the "fruits"
and "veggies" tables. Is there a way to have a field reference multiple
entries in the fruits or veggies tables? Is there a better way to
handle the whole problem?
Thanks for any help.
Ken
* 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
* Steven Klassen <sklassen@commandprompt.com> [2004-10-16 17:42:17 -0700]: > 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'); The grocery_types_id should be '2' for the last two rows. Whoops. -- Steven Klassen - Lead Programmer Command Prompt, Inc. - http://www.commandprompt.com/ PostgreSQL Replication & Support Services, (503) 667-4564
Stephen,
Thanks that does help. Syntax is a bit foreign still. I'm not sure why
you defined the "grocery_items_id_exists" and "grocery_types_id_exists
" constraints though. Is that something that's ever explicitly accessed
either through a query or an insert? Or is it one of those things that
enforces data integrity behind the scenes?
Thanks,
Ken
On Oct 16, 2004, at 8:42 PM, Steven Klassen wrote:
> * 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
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to
> majordomo@postgresql.org)
>
* Ken Tozier <kentozier@comcast.net> [2004-10-16 22:52:10 -0400]: > Or is it one of those things that enforces data integrity behind the > scenes? That's exactly it -- one of the most attractive things about the database is that if constraints are defined appropriately it can defend itself from the programmer. ;) xinu=> insert into grocery_items (grocery_types_id, name, price) values (100, 'Roast Beef', 5.50); ERROR: insert or update on table "grocery_items" violates foreign key constraint "grocery_types_id_exists" DETAIL: Key (grocery_types_id)=(100) is not present in table "grocery_types". -- Steven Klassen - Lead Programmer Command Prompt, Inc. - http://www.commandprompt.com/ PostgreSQL Replication & Support Services, (503) 667-4564
> 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
* 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
* Steven Klassen <sklassen@commandprompt.com> [2004-10-17 01:52:47 -0700]: > 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) And after I fixed the types for the latter items: xinu=> select * from items_types_view; id | item_name | type_name ----+-----------+----------- 1 | Apple | fruit 2 | Orange | fruit 3 | Brocolli | vegatable 4 | Lettuce | vegatable (4 rows) -- Steven Klassen - Lead Programmer Command Prompt, Inc. - http://www.commandprompt.com/ PostgreSQL Replication & Support Services, (503) 667-4564
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
>