E-commerce product database table structure required
Posted on 2006-04-14
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 - '
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,