[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 276
  • Last Modified:

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.
0
LeeGolding
Asked:
LeeGolding
  • 9
  • 6
1 Solution
 
Jankit141180Commented:
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
0
 
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,

Lee.
0
 
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 :-)

Lee.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Jankit141180Commented:
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

0
 
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 :-)

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


0
 
Jankit141180Commented:
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
0
 
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.

Lee.
0
 
Jankit141180Commented:
Hi

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

-Jankit
0
 
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.

Lee.
0
 
Jankit141180Commented:
ok fine.

Thank you Lee

- Jankit
0
 
LeeGoldingAuthor Commented:
Overlooked this one.

Thanks Jankit. Solution is fine.

Thanks,

Lee.
0
 
Jankit141180Commented:
Thanks
Lee
0
 
LeeGoldingAuthor Commented:
Once again sorry for the delay.

Yours,

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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 9
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now