if you read my previous database thread, i'm moving in a different direction and doing something a little more basic. i have a question that I think should be pretty basic but it's tripping me up. here is my setup:

basically i want to model a chain that sells some product (like big construction type machinery) at various catalog-based stores. the items aren't actually in stock at the various stores. so a person who wants to buy one or more products has to go to a store, talk to an employee at that store, and make an order from the catalog. Each purchase consists of 1 or more products and each product could be in one or more purchases. Each product comes from a supplier (basically my store is just a middle-man that facilitates purchases from various suppliers). After the purchase order has been placed, the shipping cost needs to be determined. So different shippers submit bids (for simplicity, they are bidding on the cost of shipping the purchase in its entirety -- not just bits and pieces of the purchase if it happens to consist of multiple products) and then the lowest bid actually gets to ship the purchase. Finally, anybody can submit an anonymous review of a product and it gets published in the catalog. So in order to submit a review you don't have to be a customer.

So in my design, i have the following tables:

Purchases
Customers
Bids
Shippers
Stores
Employees
Products
Suppliers
Reviews

- each Purchase has one Customer but each Customer can make many Purchases
- each Purchase has one Shipper but each Shipper can ship many Purchases
- each Purchase was made at one Store but many Purchases can happen at each Store
- each Purchase was made sold by one Employee but many Purchases can be sold by each Employee
- each Employee works at only one Store but each Store can have multiple Employees.
- each Purchase has one or more Products and each Product can be included in many Purchases
- each Product has one Supplier but each Supplier can supply multiple Products
- (*) each Purchase has multiple Bids but each Bid is associated with only one Purchase
- (*) each Review reviews one Product but each Product can have many reviews.

I understand how to lay out these tables in my database (I going to use Microsoft Access because it's easy to generate queries and reports) with the exceptions of the items marked with asterisks above.

For instance -- consider the Purchase table. It will have a column for "customerId" which is a foreign key that maps to the primary key with the same name in the Customer table. Similarly, the Purchase table will have foreign keys called "shipperId", "storeId", "employeeId", and "productId" that map to primary keys in the Shipper, Store, Employee, and Product tables respectively. My problem is with how to set up the Bid table with the Purchase table.

The Bid/Purchase relationship is one-to-many like all the others but it's in the opposite direction. Whereas a Customer can make multiple Purchases, a bid is for only one Purchase. How do I put a column in the Purchase table that represents the fact that there might be many Bids associated with that Purchase? I can't just put columns bid1, bid2, bid3, etc because there might be arbitrarily many Bids.

I have the same question about how to model the idea of multiple Reviews in the Product table. Any help would be appreciated.

4/26/2006 12:41:29 PM

You don't put anything in the Purchase table. The PurchaseID in the Bids table is tied to the Purchase ID and that's all you need.

Same with Reviews...
ReviewID
ProductID
ReviewRankNbr
ReviewCommentTxt
CustomerID (or however you want to track who left the review)
ReviewDt (date)

When you want to see all the reviews for a specific product its simply "SELECT * FROM ReviewTable WHERE ReviewTable.ProductID=1234"... Nothing exists in the Product table to specify any review data, its not necessary. I mean if you wanted to you could put count columns in the Product table (for reviews) and Purchase table (for bids) but it is probably excessive for your need.

[Edited on April 26, 2006 at 1:19 PM. Reason : .]

4/26/2006 1:09:49 PM

hahah

theres an ongoing joke here at work

that our director is a "Access Developer"

ok, sorry, back on topic, I'll lend my guidance later when said Access Developer ain't watching over my shoulder.

4/26/2006 1:12:04 PM

^^ that makes a lot of sense but i'm leery because our teacher always talks about mapping primary keys to foreign keys.

i guess the exact same argument you gave describes why I won't have a column in the Customer table for "purchases". Instead, if I want to list all purchases made by a certain customer then I do:

"SELECT * FROM PurchaseTable WHERE PurchaseTable.CustomerID=1234"

4/26/2006 1:28:03 PM

and for Maugan -- I'd like to hear whatever you have to say about this. so chime in whenever you get a chance. i've never used access before so i don't really know what i'm getting myself into. but the other people in my class say to use it because the alternative is to:

1. download and install apache
2. download and install php
3. figure out how to get php to talk to the database
4. write php code in webpages to generate queries and reports

in Access, they tell me that there is a "Wizard" to generate queries and that it's all self-contained.

so that's why i'm going to go that route (plus it got installed on my computer with Microsoft Office)

4/26/2006 1:32:33 PM