Database design for ecommerce site

[adammc]

Hi Guys,

I am looking to design an ecommerce site with categorised products.
Once built, I want my client to be able to add additional categories and be abel to add their products into their chosen category.

Would I be best displaying this dynamnically after creating a table with the products category names in it or doing it static?

Also, if I wanted to show a different header / meta tags for weach category how would I best tie this in?

I have designed a few ecommerce sites in the past but have done it the hard way, creating each individual category page!

Any advice would be GREATLY appreciated :)

[mattur]

Basically two ways of handling product categories:

1:Many - each category can have many products, but each product can only be in one category. Here your table design would be something like:

tblProduct: ProdID ¦ ProdTitle ¦ CatID ¦ etc
tblCategory: CatID ¦ CatName ¦ etc

Many:Many - each category can have many products, and each product can be in many categories. Table design would use third table to express links between Product and Category tables:

tblProduct: ProdID ¦ ProdTitle ¦ etc
tblCategory: CatID ¦ CatName ¦ etc
tblProductCategory: ProdID ¦ CatID

Adding headings, metadescrip etc for category pages:

tblCategory: CatID ¦ CatName ¦ CatHeader ¦ MetaDescrip ¦ etc

Not sure what you mean by doing it static, but here's a couple of useful threads about ecommerce db design:

E-Commerce Database design [webmasterworld.com]

Database Structure Design [webmasterworld.com]

[adammc]

Thanks for the great tips :)
How would I then best build the page that shows the products and differnent metas in each category?

I have done it the hard way in the past, creating static category pages.

[mattur]

How would I then best build the page that shows the products and differnent metas in each category?

However you want to ;) You can have dynamic category pages passing a CatID pulling info from the db, similar to how you would pull info on a product page. Or you could write code to pull info from the db and then generate your "static" pages. It's up to you.

If you have specific coding questions you may want to start a new thread in the relevant forum (eg ASP, PHP etc). HTH