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,

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Hi Lee

First of all consider that you have a product A.
As per your spec, A has many sizes with different weight combination with volumes and each will be having different prices.

Here i woule suggest to differentiate these as 'attributes' for item of product.

Make a table 'attributes' having attrid autonumber (PK), title. Here title will be having size, weight and volume.

Make a table 'attribute_detail' having adid autonumber (PK), attrid(FK), strval. Here strval will be '10mm' or '5kg' or '1lts' etc as per its attrid.

Now make a table product_item and in which you could record all the possible values as much possible. Say 'product_item' having piid, pid, adid, attrid, price.

In ACCESS. query will be much easier as it provides query analyzer.

Happy programming.

- Jankit

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LeeGoldingAuthor Commented:
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,

LeeGoldingAuthor Commented:
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 :-)

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

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

LeeGoldingAuthor Commented:
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 :-)

LeeGoldingAuthor Commented:
I've created the db as per your specs.


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



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
LeeGoldingAuthor Commented:
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.


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

LeeGoldingAuthor Commented:
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.

ok fine.

Thank you Lee

- Jankit
LeeGoldingAuthor Commented:
Overlooked this one.

Thanks Jankit. Solution is fine.


LeeGoldingAuthor Commented:
Once again sorry for the delay.


LeeGoldingAuthor Commented:

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:

1 - Size
2 - Weight
3 - Volume

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

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

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:


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


It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Development

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.