Need help structering a MS Access dB 2

[GUJUm0deL]

I'm designing a web site for a client that also wants to sell his products on his site. But I cam accross a slight dB related problem.

Originaly, I had this set-up
table: products
ProductID (PK)
CategoryID
ProductName
UnitSize1
UnitSize2
UnitSize3
UnitSize4
UnitPrice1
UnitPrice2
UnitPrice3
UnitPrice4
DescriptionShort

Then I created the site, and all went according to plan, except when I tried to delete a specific product of a specific size from the shopping cart, when I did that all sizes of that product name got deleted. I realized why that happened, all 3 (or 4) sizes have the same productID.

So then I re-tooled, so now I have this set-up:
table: products
ProductID (PK)
CategoryID
ProductName
UnitSize
UnitPrice
DescriptionShort

When I do it this way, I can delete a specific product name of a spcific size, and all's well with the world. My only problem is the visual aspect of this now. Before I listed each product name once, and now each product name is listed 3 times (because of the diff. sizes), which makes the dB larger.

What would be the optimal way of desiging the dB, to do what I want?

Thanks.

_{Reality continues to ruin my life...}

 

[alr0]

Hi,

The first thing I always recommend is to take the time to identify all of your business requirements as a first step. It is probably worth some time now before putting a lot of time into changes.

One way to tackle this issue is to have only one product record as you did before but add a product detail table that is related by a product primary key to as many sizes with statistics as necessary.

tblProduct

ProductID (Primary key)
Name
mfg
etc.

tblProductDetail

ProductDetailID (primary key)
ProductID (foreign key)
size
colour
etc.

Then you control over all items for sale that come from the ProductDetail table. Does that make sense and address your dilemma?

al

 

[GUJUm0deL]

alr0, thanks for getting back to me. I think I understand what you're trying to say. But I am slightly confused. Since one product has 3 diff. sizes (and some have an extra special packing size), how can having a tblProductDetail help me with the shopping cart?

Let's say the client clicks on "ProductA" (and productA has 3 sizes, size1, size2, size3), how can I then use the new dB setup to display the three sizes, allow the user to only order for the size they want, and continue on?

I hope this doesn't sound overtly complicated.

This is what I have done so far:

http://www.gujum0del.com/shopping/store.cfm

Thanks.

_{Reality continues to ruin my life...}

 

[JonFer]

Have you looked into a crosstab query? If the Spice and Nut sizes are always the same within the category, you could use UnitSize as your ColumnHeading field in the crosstab query and use UnitPrice as the Value field. Try it out and see what you get.

 

[JeremyNYC]

I don't think a crosstab addresses the issue. The issue is the table structure. It wouldn't be difficult to delete just the data you wanted to delete, if you used an update query instead of a delete query. But it would still be a bad idea to structure your table that way. You mentioned that products come in three sizes, except some that come in four. What happens the first time your client tries to stock a product that comes in four sizes. To deal with this, take Al's advice, and break the size information out into another table.

To get an understanding of the reasons for doing this, check out Paul Litwin's "The Fundamentals of Relational Database Design". There's a copy in the Developers' section of my website. If you're going to be building databases for clients, you'll definitely want to know some more about database theory.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[JonFer]

The crosstab query is for displaying the products after the database is normalized.

 

[JeremyNYC]

Jon,

Ah. Now I see what you're saying. I thought you were telling the original poster to just use a crosstab, and that that would solve their problem without having to normalize. Sorry for my confusion.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[alr0]

Hi GUJUm0det,

My thought was to have customers order products from the product detail table once they have selected a product. tblProductDetail will contain a record for each permutation of size and other attributes. Further, if a size is sold out or unavailable for any reason it can be removed without changing the other offerings. This way you will have an unlimited number of sizes, colours, extra special size, etc.

When a user chooses product A, the related records in product detail will be displayed for the user to select which ever they prefer. Users will be purchasing items from the tblProductDetail table only. The product table will only direct them to what is available.

Is that a little clearer?

al

 

[GUJUm0deL]

alr0, I think I understand what you mean. What your saying is:
1) First the users sees the products (which are displayed from the tblProduct table),
2) They click on a specific product, where a new page loads that displays all the sizes and prices for that paticular product. Also, on this screen they can stipulate the quantity they want for any sizes.
3) Then the user can go back to order more, or checkout.

Correct?

But if they wanted to delete a paticular item then the delete query would refer to the productID from the tblProduct or the ProductDetailID from the tblProductDetail??





JeremyNYC, hi thanks for replying. I d/l Paul Litwin document, haven't read it yet, but will print it out, so I can read it on the way home from work today. Thanks.

_{Reality continues to ruin my life...}

 

[alr0]

Hi GUJUm0det,

Yes, the only thing I would clarify is that if you need to delete a size, colour etc. this can be done only in tblProductDetail. To delete an entire product family a deletion in tblProduct should include the deletion of all related records in tblProductDetail. (cascading delete?)

al