asked on

E-commerce product database table structure required

Hi friends,

I am developing an fishing bait e-commerce shopping cart website. Using ASP and MS Access. The user will be able to administer the products (add, remove, delete) via a web interface and HTML forms.

The complication is the products and options available. Such as size, weights, units, etc. I have come up with a table for categories and products. But don't know if I should have a different product table for each of the product types below, or one table with all attributes in (such as sizes, prices, etc).

There are 6 categories and around 120 products. So around 20 products in each category.

The way I see things there are 3 types of products:

1) Products that are in one size (say 10mm), but are available in any number of weights depending on the weight of the product in question. E.g. 'FishyChunks 10mm - 1kg @ £4.99, 5kg @ £19.99, etc. On a product page listing, the weights and prices need to be in a combo box.

2) Products are are bottled and available in different volumes and a unique price for each. E.g. 50ml - £3.99, 250ml - £9.99.......

3) Products that are in multiple sizes and prices, but each size available in any number of weights depending on the weight of the product in question. E.g. 'FishyChunks 10mm - 1kg @ £4.99, 5kg @ £19.99, etc'. 'FishyChunks 14mm - '

On a product page listing, the sizes would be in a combo box. And the weights for that size available in another combo box. When the user changes a size and/or weight, some javascript will update the price of the item.

Each category can have a combination of any of the product types above.

I'm having trouble getting a good database strucutre together on paper. The main consideration is that I can the website done as quickly as possible :-) So i'm not worried about perfectly normalized db design. I want to be able to write the ASP as quickly as possible, that gives me the least trouble in:

a) Allowing the website users to browse categories and add items to their basket
b) Allowing the site administrators to add/remove/change products. Different types of products will need to have different combos available to set sizes, weights, prices, etc.

Very complicated! For me anyway :-)

Thanks in advance,

Lee.

ASKER CERTIFIED SOLUTION

Jankit141180

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

LeeGolding

ASKER

1) Not quite. Product type A has many sizes with different weight combinations, and each having different prices. But there are no volumes in product type A. E.g.

Product 1's Available:
'Product 1' -> 10mm 1kg=£10.99, 2.5kg=£21.99, 5kg=£48.99
'Product 1' -> 14mm 1kg=£9.99, 2.5kg=£19.99, 5kg=£45.99
'Product 1' -> 18mm 1kg=£9.99, 2.5kg=£19.99, 5kg=£45.99

2) In the product_item table, where does 'pid' field come from???

Thanks so far,

Lee.

LeeGolding

ASKER

By the way, I don't use the query builder in MS Access, I could never get my head round it. I prefer to write my queries in pure SQL. I am quite familiar with SQL, but have never done a product / e-commerce structure before :-(

So I will need at some point SQL statements to get a insert a product OR select a product from the database, and depending on its type, its sizes and/or weights and/or volumes. I'm sure we will get to this later however :-)

Lee.

Jankit141180

Hi Lee

1) I agree that Product type A has many attributes whether it has volume or not, your insert statement for each item will remain same, if product does not have volume then insert for that particular (volume's) 'attrid' and / or 'adid' will not happen.

2) In the product_item table, 'pid' field comes from your parent product table. I have used this name as a reference only.

do let me know in case any further doubts

- Jankit

LeeGolding

ASKER

I see. I'll build the tables and see if I can get some SQL statements working. Then I'll get back to you.

Thanks so far :-)

Lee.

LeeGolding

ASKER

I've created the db as per your specs.

http://myweb.tiscali.co.uk/leegolding/db.zip

However, I can't record the information as I need to. I need to record these values:

Squidutti, 10mm, 1kg, £10.99
Squidutti, 10mm, 2.5kg, £16.99
Squidutti, 10mm, 5kg, £44.99
Squidutti, 14mm, 1kg, £9.99
Squidutti, 14mm, 2.5kg, £14.99.99
Squidutti, 14mm, 5kg, £39.99

Using tblProductItem, this is not possible. I can however record the following (which is not enough):

Squidutti, 10mm, £10.99
Squidutti, 14mm, £16.99
Squidutti, 18mm, £44.99

Lee.

Jankit141180

Hi.

I have studied the database in detail.

Replace attrId from tblAttributeDetail to aID as it referes to tblAttributes.

For each size you have to insert recod in tblAttributeDetail.
Like insert tblAttributeDetail (aId, strval) values (1, 10mm), insert tblAttributeDetail (aId, strval) values (3, 1kg) etc

In tblProductItem insert record like
for Squidutti, 10mm, 1kg, £10.99

insert into tblProductItem (pID, adID, aId, price) values (1, 4, 3, 10.99) and so on.

Its very simple. Try it.

- Jankit

LeeGolding

ASKER

Sorry for the delay. I've other work that's taking up a lot of my time :-(

I'll get back to your ASAP and haven't forgotton the question.

Lee.

Jankit141180

Hi

If this is satisfactory, please accept the answer. Do let me know if there are any doubts.

-Jankit

LeeGolding

ASKER

I meant, I haven't had time to try your last comment. Please give me some time!!!

If it works when I try it I will award the points.

Lee.

Jankit141180

ok fine.

Thank you Lee

- Jankit

LeeGolding

ASKER

Overlooked this one.

Thanks Jankit. Solution is fine.

Thanks,

Lee.

Jankit141180

Thanks
Lee

LeeGolding

ASKER

Once again sorry for the delay.

Yours,

Lee.

LeeGolding

ASKER

Jankit,

Now I've had some time to do this, it really doesn't seem to work as required.

It will store product sizes and weights, but not correctly. With each weight having it's own price. According to your specification, 'product_item' table would have:

tblAttribute
-------------
1 - Size
2 - Weight
3 - Volume

tblProduct
------------
pID - cID - strName - strDescription
1 - Rolled Baits - Squidutti - 'Based on squid, shrimp and crustean powders and extracts.'

tblAttributeDetail
-------------------
adID - aID - strVal
1 - 1 - 14mm
2 - 1 - 16mm
3 - 1 - 18mm
4 - 2 - 1kg
5 - 2 - 2.5kg
6 - 2 - 5kg

tblProductItem
-----------------
pID - adID - aID - price
Squidutti - 14mm - Size - £0.00
Squidutti - 16mm - Size - £0.00
Squidutti - 18mm - Size - £0.00
Squidutti - 1kg - Weight - £5.50
Squidutti - 2.5kg - Weight - £13.50
Squidutti - 5kg - Weight - £26.50

1) This does not seem logical. Why store a price for a certain size, when the price for that size depends on the weight avaiable for it? Eg. 14mm @ 1kg = £5.50. 14mm @ 2.5kg = £13.50. 18mm @ 1kg = £5.50, and so on.

2) Also, what about for items for example 10mm - 1kg - £6.50. 12mm - 1kg = £6.50. This is because for this product, sizes 10mm and 12mm are more expensive per KG. In this instance, 1kg of 10mm cost £1.00 extra, so £6.50 rather than £5.50 for the 14mm, 16mm, 18mm versions.

Can you help me out with this one. If not, I can will have to post a new question :-(

The database is at:

http://myweb.tiscali.co.uk/leegolding/db.zip

I'm now on a deadline for this project so my replies will be very prompt when you reply :-)

Thanks,

Lee.