Welcome Guest, Not a member yet? Register   Sign In
Little help with database design (ecommerce, mysql)
#1

[eluser]Vicente Russo[/eluser]
Hello guys, now I need some help.

I`m developing an ecommecer from scratch, and I would like to organize products in categories and 'tags' (or similar features). Example. I have "Tv & Video" (Category), Television (Subcategory), and then 3rd depth link (could be something like Flat-Panel TVs, Projection TVs, Projectors & Screens, TV/DVD Combos) and 4th depth link (could be anything).

But I can filter by these common features, like display size, brand, price range, resolution, etc. A nice example is Best Buy. How do I model a database just like that?

Thanks!
#2

[eluser]Eric Barnes[/eluser]
I always set my categories up with a parent_id int field. Something like:
id, parent_id, name
1, 0, Parent
2, 1, Child of parent
3, 0, Another parent
4, 2, Child of child 2.
etc...

Then for the products I just have a normal products table that is used in the sorting. Possibly using a secondary product_fields table to hold special extra fields. But probably not needed.
#3

[eluser]Vicente Russo[/eluser]
Sorry, I don`t understand. How should be the category table and product table? I tried to do something like you said, but I think it`s not correct.
#4

[eluser]theshiftexchange[/eluser]
I'd do something like:

Product_Table:
ProductID
ProductName
PRIMARYKEY: ProductID


Category_Table:
CategoryID
CategoryName
PRIMARYKEY: CategoryID


CategoryProduct_Table:
ProductID
CategoryID
PRIMARYKEY: ProductID,CategoryID


This will allow you to have 'multiple categories' listed for a single product
#5

[eluser]Thorpe Obazee[/eluser]
[quote author="theshiftexchange" date="1241776665"]
PRIMARYKEY: ProductID,CategoryID[/quote]

You can't have two primary keys.
#6

[eluser]theshiftexchange[/eluser]
[quote author="bargainph" date="1241777647"][quote author="theshiftexchange" date="1241776665"]
PRIMARYKEY: ProductID,CategoryID[/quote]

You can't have two primary keys.[/quote]

In MySQL you can have a "primary key" which is made up of 2 fields. Otherwise known as a "multi-field primary key"
#7

[eluser]Thorpe Obazee[/eluser]
Yeah, I just read about that.
#8

[eluser]Vicente Russo[/eluser]
And what about the common features?
#9

[eluser]Dam1an[/eluser]
Well, you could have a common_fields table which has a row for everything, this covers things which every item will have (price and brand spring to mind)
You would then also have a common_fields table for each top level category, so you couold have tv_common_fields with size, res etc
When doing a query, you would need to join the global and category common field tables on the product ID

You can then just do a load of where clauses to get what you want
#10

[eluser]Jondolar[/eluser]
I just added this feature to my shopping cart. My approach was to create an attribute table for the "attribute type" and "attributes" and an attribute_product table for the many <-> many relationship and an attributes_attributeproduct table to hold the individual attributes for each product (which allows each product to be found under multiple attributes). Each product can have multiple attributes and each attribute can have multiple products. The key for me was that an attribute had a parent categoryid so each product had to fall under that parent category (be a child/sub-child).

An attribute type would be Memory, HD Size, Ram Size, etc. An attribute would be 512MB, 1GB, 2GB (for memory as an example). So the attributes_attributeproduct table has an entry for every attribute that that product has for every attribute type.

Hmm, this was actually easier to implement than to describe.

Key points:
You may want your products in sub-categories to have the same attribute types as parent categories in some cases.
You may want a product to have multiple attributes (product may come in multiple colors or have options for adding more memory so you will want to find the product no matter how much ram they chose).
You may want the search to allow for multiple attributes to be selected per attribute type. Best Buy does not do this but I found it a very nice addition.

Good luck with your project.




Theme © iAndrew 2016 - Forum software by © MyBB