[thelist] SQL query statement help

[Chris Blessing]

Belinda-

Several things could have been done differently, but your design is typical of that of the beginner dba (I used to ALWAYS envision tons and tons of tables, then I got clued in by a friend that you didn't really need to do that).

Let's go with this scenario of 3 tables:

Items
	-> item id (primary key)
	-> item_type_id (foreign key from item_types)
	-> item name
	-> any other item-level details you want to store

Bids
	-> bid id (primary key)
	-> item id (foreign key from items)
	-> bid amount

Item_Types
	-> item type id (primary key)
	-> item type

This would allow you to store all the items in the auction under one master table.  You could then form the above relationships which would allow you to selectively show data based on certain criteria (i.e. show all items in the auction, show all items under $50, do a sum of all bids, show all items of one particular type, etc.).  The item_types table would be pre-filled by you, assuming you knew what all the various types were ahead of time.  More could be added of course as necessary.  Imagine this query to show all the items and their bids of the type "angels":

SELECT a.item_id, a.item_name, b.bid_amount
FROM items a, bids b, item_types c
WHERE a.item_id = b.item_id
	AND a.item_type_id = c.item_type_id
	AND c.item_type = 'angels'

You could write tons and tons of queries around this structure, all very basic joins, allowing you to get whatever information you need.  To get the sum of all bids:

SELECT sum(bid_amount)
FROM bids

Or to get even more specific (the sum of all bids placed on items of the type "angels"):

SELECT sum(a.bid_amount)
FROM bids a, item_types b, items c
WHERE a.item_id = c.item_id
	AND c.item_type_id = b.item_type_id
	AND b.item_type = 'angels'

I'm sure this can be further normalized or optimized, but that's a good start.

Good luck!

Chris Blessing
webguy at mail.rit.edu
http://www.330i.net

> -----Original Message-----
> From: thelist-admin at lists.evolt.org
> [mailto:thelist-admin at lists.evolt.org]On Behalf Of Belinda Johnson
> Sent: Monday, December 09, 2002 1:48 PM
> To: thelist at lists.evolt.org
> Subject: RE: [thelist] SQL query statement help
>
> I would welcome
> suggestions on how
> I SHOULD have set this up - as I know this community is going to
> want to do
> this every year going forward.
>
> Thansk for your advice & assistance.
>
> Belinda