Database Design

[kenkja]

Hello I'm a newbie to os-commerce, as of last week.

 

With your help I have been able to install the system using my pc as a local service.

 

I've also managed to get a grip on altering the "look & feel" of the storefront.

 

Thanks for all your help so far.

 

So I set about adding products, so far I've added 18 & got the category tree down quite well, but I've noticed that the 18 products have already produced over 150 product_attributes.

 

Virtually every product I sell is available in a number of colours and sizes, etc, so with hundreds of products to add, its quite possible I may end up with 1000's of product_attributes.

 

Is this likely to cause any problems, for example in terms of load speed.

 

Thanks

[♥bruyndoncx]

yes, but you can optimize and add indexes

 

more important question, will you need to track stock for each combo, if so, you need to look at qtpro

 

and lastly, the new version (still in development) has the attributes system rewritten ...

[kenkja]

yes, but you can optimize and add indexes

 

more important question, will you need to track stock for each combo, if so, you need to look at qtpro

 

and lastly, the new version (still in development) has the attributes system rewritten ...

 

 

Thanks, stock tracking is not required, what is qtpro ?

[kenkja]

Thanks, stock tracking is not required, what is qtpro ?

 

Hi thanks have found qtpro, pretty sure its not what I need. I've been working some more on the database and have some issues - my aplogies for the long post.

 

The site will be for motorcycle parts, clothing & accessories. It is the clothing category which requires multiple product attributes whilst the parts can be split between manufacturers and after market parts.

 

As an example I've uploaded over 1500 parts from one manufacturer, who has only been in business for 11 years, there are other manufacturers i'd like to add who have in excess of 20,000 parts. For the parts loaded to date - they are for either trials or enduro bikes, either for 4 or 2 stroke engines, can be appropriate for for models manufactured from 1999 to 2010, can be for up to 5 different engine sizes and can up for up to 18 "areas" of the bike. So for example the "area" may be the "Rear Wheel" it consists of around 40 different products which can be appropriate for only 1 engine size for a single year but may cover numerous engines and numerous years and some may fit both trials & enduro bikes. The same will be true of the manufacturers with more parts.

 

On my office live existing website I had either a menu tree which led to

 

1 A Manufacturer

2 A type of bike (trial or enduro)

3 A year

4 A drop down list of parts for that year

 

or

 

1 A Manufacturer

2 A Type of bike (trial or enduro)

3 A Group of years - say 2003-2009

4 A larger drop down list which showed the appropriate part number for a range of years

eg Part no 1 2003-2005 / Part no 2 2006-2009

 

As mentioned elsewhere in the forum this is not very SEO friendly nor is good for the likes of Google Products

 

So my initial plan was to upload each part individually and then create a "menu" tree which allows users to find the type of bike, manufacturer, year, "area" of bike etc.

 

After uploading the 1500 parts I have realised that there are number of issues with this.

 

Firstly images do not exist for these components individually (never mind for manufacturers with 20000 parts), the images are of the parts diagram for an "area" of the bike. Its just not practical, as I will not live long enough to use the parts diagrams to make individual images. So its one image for all components in an area of a bike however, this could be misleading as some items appear in different "areas".

 

So its either list the part numerous time & link to the appropriate image. Or if possible have a product attribute which somehow shows an alternative image dependant upon where the user is within the menu/category tree - is this possible.

 

Secondly at each level prior to the actual product page the system displays (i think) a shortened version of the field "product_model" from the products database, logically it makes senses for this to be the actual part number - say part number "B/C921" - but unless the user actual knows the part number he's looking for its not very user friendly.

 

Can I change the field used to product_name from the product_description database and as this is a much longer field is the system able to word wrap the result to keep displaying the usual amount on thumbnails across the page.

 

Thirdly if when completed the site has say 40000 products will it just be too big to work with reasonable load times

 

Thanks for reading this far, I hope you have not lost the will to live !

 

Ken

[kenkja]

Hi thanks have found qtpro, pretty sure its not what I need. I've been working some more on the database and have some issues - my aplogies for the long post.

 

The site will be for motorcycle parts, clothing & accessories. It is the clothing category which requires multiple product attributes whilst the parts can be split between manufacturers and after market parts.

 

As an example I've uploaded over 1500 parts from one manufacturer, who has only been in business for 11 years, there are other manufacturers i'd like to add who have in excess of 20,000 parts. For the parts loaded to date - they are for either trials or enduro bikes, either for 4 or 2 stroke engines, can be appropriate for for models manufactured from 1999 to 2010, can be for up to 5 different engine sizes and can up for up to 18 "areas" of the bike. So for example the "area" may be the "Rear Wheel" it consists of around 40 different products which can be appropriate for only 1 engine size for a single year but may cover numerous engines and numerous years and some may fit both trials & enduro bikes. The same will be true of the manufacturers with more parts.

 

On my office live existing website I had either a menu tree which led to

 

1 A Manufacturer

2 A type of bike (trial or enduro)

3 A year

4 A drop down list of parts for that year

 

or

 

1 A Manufacturer

2 A Type of bike (trial or enduro)

3 A Group of years - say 2003-2009

4 A larger drop down list which showed the appropriate part number for a range of years

eg Part no 1 2003-2005 / Part no 2 2006-2009

 

As mentioned elsewhere in the forum this is not very SEO friendly nor is good for the likes of Google Products

 

So my initial plan was to upload each part individually and then create a "menu" tree which allows users to find the type of bike, manufacturer, year, "area" of bike etc.

 

After uploading the 1500 parts I have realised that there are number of issues with this.

 

Firstly images do not exist for these components individually (never mind for manufacturers with 20000 parts), the images are of the parts diagram for an "area" of the bike. Its just not practical, as I will not live long enough to use the parts diagrams to make individual images. So its one image for all components in an area of a bike however, this could be misleading as some items appear in different "areas".

 

So its either list the part numerous time & link to the appropriate image. Or if possible have a product attribute which somehow shows an alternative image dependant upon where the user is within the menu/category tree - is this possible.

 

Secondly at each level prior to the actual product page the system displays (i think) a shortened version of the field "product_model" from the products database, logically it makes senses for this to be the actual part number - say part number "B/C921" - but unless the user actual knows the part number he's looking for its not very user friendly.

 

Can I change the field used to product_name from the product_description database and as this is a much longer field is the system able to word wrap the result to keep displaying the usual amount on thumbnails across the page.

 

Thirdly if when completed the site has say 40000 products will it just be too big to work with reasonable load times

 

Thanks for reading this far, I hope you have not lost the will to live !

 

Ken

 

Sorry about this, just realised that post is in wrong forum, it should be v2.x not v3.x

[♥bruyndoncx]

maybe the products specifications module is what you could use to structure your site.

 

master products allows for one picture with the master products and a list of slave products listed below it. There are different configuration options , one of them might suit you.

[kenkja]

maybe the products specifications module is what you could use to structure your site.

 

master products allows for one picture with the master products and a list of slave products listed below it. There are different configuration options , one of them might suit you.

 

Hi thanks for that, will take a look.

 

ken