Store webshop products as nodes or in a custom table
Hello,
I want to start a webshop and I'm not sure which is the best way to store the products. The webshop has multiple languages and each language can have their own products (but some products are in multiple languages).
Here are the 2 options:
1 Store the products as nodes (and save them as xml).
2 Store the products in a new table in SQL server.
Both have their pros and cons:
1 Store the products as nodes (and save them as xml)
Pros: -Automatic url rewriting -Less time to develop (no need for an extra section) -Products can be displayed with XSLT Cons -Can’t store thousands of products because the xml might be too big -Searching option might be harder on xml than on a sql table
2 Store the products in a new table in SQL server.
Pros: -Might be easier to maintain depending on the setup of the custom section -Might be easier to extend in the future -Searching option could be more advanced (but not sure if that’s
necessary) -Only fetch the data which is required (with xml you’ll get the
description for all languages for example) Cons -More time to develop -No Automatic url rewriting -Could be done with XSLT, but you’ll need XSLT extensions -Probably slower because you’ll need to fetch data from the database
instead of cached xml
I've already been emailing with a colleague about this. Here is his opion:
I think I'd say that it's six to one and half a dozen to the other :)
... except I'd come out on the side of storing the products in the database natively. I've done both, and it ends up being a whole lot less painful this way in my opinion.
To address your cons list for the in-database side:
- it may take a little longer to develop (marginally) but the benefit to the user of maintaining their catalogue in a dedicated section far outweighs this.
- you can use the built-in rewriting for this which works really well and takes very little time to set up. This then feeds into a standard page in the content tree and will work with user controls or XSLT equally well. (Note that there is a gotcha with the user controls though - see UrlRewriterFormRewriter in the Eurofysica.GeneralCode project)
- XSLT extensions are pretty easy, and you can get a custom XML tree out without much effort (again, see the EF extensions in the UmbracoExtension project)
- Regarding performance, I think it unlikely that the database storage will prove to be a bottleneck. If it is, there are lots of tools at your disposal: the umbraco macro cache, or simply using the ASP.NET cache will flexibly allow you to boost your performance beyond the point where its a problem. Also, the backend will scale far better with this approach.
In general, it's worth noting the number of projects, including Blog4Umbraco, that use database tables. There are a lot of use cases where it just makes sense to do so. Besides, with Entity Framework or LLBLGen your development time is markedly reduced to what it would be without ORM.
Now I'd like to hear other people their opinion about this :). Store as nodes or in a custom table?
I've never thought that the tree structure was suitable for representing the categories in an online shop, and for me this alone was enough of a dealbreaker to not use nodes for shop data.
- What about too much products? - What about cross-categorisation?
@dandrayne Yes the cross-categorisation could be difficult. See this topic: http://our.umbraco.org/forum/using/ui-questions/10375-Multiple-language-webshop-products. In this case there probably isn't any cross-categorisation (if a product has a different language it could be a different node with it's own description and price) so that wouldn't be a problem.
It won't be too much products (max 200 per language), but an overview of all products in a new section still has a better overview.
I think I'm going for the custom table. Anyone else who can convince to use nodes ;)?
Not gonna try to convince you to use nodes, but it's a very interesting discussion - nodes vs. custom tables :)
In this case I agree with Dan and your colleague. If you're dealing with hundreds of products and moderately complex language/categorization, the extra time spend on development would propably be a good investment in terms of usability and extensibility. But if the products and business logic very rarely change, nodes might be the better option after all.
If you go for custom tables, you may wish to take a look at http://our.umbraco.org/projects/developer-tools/dewd which I made for generic table editing. Not sure how to do the language stuff you're describing in an elegant way, but maybe it can provide some inspiration for doing content editing with custom tables.
DEWD looks nice, but it's not the way I want to present the data to the user. I'll create my own custom section which will handle this. Still nodes vs cutsom tables is an interesting discussion :). So far custom tables are the way to go. I wonder how this is done with SuperSimpleWebshop and uCommerce.
uCommerce uses a custom database to store product information. We do this to be able to scale to a very large number of products and also to be able to categorize and cross reference products however the clients wants.
Our philosophy is that a product should be a self-contained unit of data placable anywhere in the hierarchy, i.e. in any store, any catalog, and any category. One of the finer points with this approach is that you won't get any duplicate data, which can be quite the hassle for an online store sellinh a wide range of products or has a very deep assortment. The redundant data is a deal breaker in this case because just keeping one version of each product is close to an unmanageable task for many store owners.
The disadvantage to using a custom database is that you really need to know what you're doing to achieve the required performance from the database and you will be spending a lot of time building the management UIs for the catalog; again a non-trivial task if you're dealing with fully customizable product template like we do in uCommerce with our product definitions. In essence all UIs become a matter of reflecting over each type of product and generating a fitting UI for that particular type.
However in the long run I believe (naturally :)) that this is the way to go because the end result will have far more flexibility and will be able to grow with the business as needed.
A detail which becomes a lot easier to deal with is installation. The primary integration between uCommerce and Umbraco is done on the UI level (for the management trees) and in a few instances on the data level: Integration with members, media library, and so forth. The advantage here is that we deal with upgrade scenarios only for our own stuff. Say we need to migrate the database from version 1 to 2; we only really need to deal with our own database. I cringe at the though of having to do the same thing for document types and content nodes holding the products.
We use nodes in SuperSimpleWebshop. We are using tags to accomplish cross-categorisation. This way the whole commerce section works exactly as anything else in Umbraco. For the target market of SuperSimpleWebshop this works, without doubt, the best. (it's not meant for large shops!)
We do some very nice tricks with the Umbraco API, to keep SuperSimpleWebshop as integrated with Umbraco as possible. This is more work than custom tables, but in the end it's a much more versatile solution.
Store webshop products as nodes or in a custom table
Hello,
I want to start a webshop and I'm not sure which is the best way to store the products. The webshop has multiple languages and each language can have their own products (but some products are in multiple languages).
Here are the 2 options:
1 Store the products as nodes (and save them as xml).
2 Store the products in a new table in SQL server.
Both have their pros and cons:
1 Store the products as nodes (and save them as xml)
Pros:
- Automatic url rewriting
- Less time to develop (no need for an extra section)
- Products can be displayed with XSLT
Cons
- Can’t store thousands of products because the xml might be too big
- Searching option might be harder on xml than on a sql table
2 Store the products in a new table in SQL server.
Pros:
- Might be easier to maintain depending on the setup of the custom section
- Might be easier to extend in the future
- Searching option could be more advanced (but not sure if that’s necessary)
- Only fetch the data which is required (with xml you’ll get the description for all languages for example)
Cons
- More time to develop
- No Automatic url rewriting
- Could be done with XSLT, but you’ll need XSLT extensions
- Probably slower because you’ll need to fetch data from the database instead of cached xml
I've already been emailing with a colleague about this. Here is his opion:
I think I'd say that it's six to one and half a dozen to the other :)
... except I'd come out on the side of storing the products in the database natively. I've done both, and it ends up being a whole lot less painful this way in my opinion.
To address your cons list for the in-database side:
- it may take a little longer to develop (marginally) but the benefit to the user of maintaining their catalogue in a dedicated section far outweighs this.
- you can use the built-in rewriting for this which works really well and takes very little time to set up. This then feeds into a standard page in the content tree and will work with user controls or XSLT equally well. (Note that there is a gotcha with the user controls though - see UrlRewriterFormRewriter in the Eurofysica.GeneralCode project)
- XSLT extensions are pretty easy, and you can get a custom XML tree out without much effort (again, see the EF extensions in the UmbracoExtension project)
- Regarding performance, I think it unlikely that the database storage will prove to be a bottleneck. If it is, there are lots of tools at your disposal: the umbraco macro cache, or simply using the ASP.NET cache will flexibly allow you to boost your performance beyond the point where its a problem. Also, the backend will scale far better with this approach.
In general, it's worth noting the number of projects, including Blog4Umbraco, that use database tables. There are a lot of use cases where it just makes sense to do so. Besides, with Entity Framework or LLBLGen your development time is markedly reduced to what it would be without ORM.
Now I'd like to hear other people their opinion about this :). Store as nodes or in a custom table?
Jeroen
I've never thought that the tree structure was suitable for representing the categories in an online shop, and for me this alone was enough of a dealbreaker to not use nodes for shop data.
- What about too much products?
- What about cross-categorisation?
Dan
@dandrayne Yes the cross-categorisation could be difficult. See this topic: http://our.umbraco.org/forum/using/ui-questions/10375-Multiple-language-webshop-products. In this case there probably isn't any cross-categorisation (if a product has a different language it could be a different node with it's own description and price) so that wouldn't be a problem.
It won't be too much products (max 200 per language), but an overview of all products in a new section still has a better overview.
I think I'm going for the custom table. Anyone else who can convince to use nodes ;)?
Jeroen
Not gonna try to convince you to use nodes, but it's a very interesting discussion - nodes vs. custom tables :)
In this case I agree with Dan and your colleague. If you're dealing with hundreds of products and moderately complex language/categorization, the extra time spend on development would propably be a good investment in terms of usability and extensibility. But if the products and business logic very rarely change, nodes might be the better option after all.
If you go for custom tables, you may wish to take a look at http://our.umbraco.org/projects/developer-tools/dewd which I made for generic table editing. Not sure how to do the language stuff you're describing in an elegant way, but maybe it can provide some inspiration for doing content editing with custom tables.
- Sune
DEWD looks nice, but it's not the way I want to present the data to the user. I'll create my own custom section which will handle this. Still nodes vs cutsom tables is an interesting discussion :). So far custom tables are the way to go. I wonder how this is done with SuperSimpleWebshop and uCommerce.
Jeroen
uCommerce uses a custom database to store product information. We do this to be able to scale to a very large number of products and also to be able to categorize and cross reference products however the clients wants.
Our philosophy is that a product should be a self-contained unit of data placable anywhere in the hierarchy, i.e. in any store, any catalog, and any category. One of the finer points with this approach is that you won't get any duplicate data, which can be quite the hassle for an online store sellinh a wide range of products or has a very deep assortment. The redundant data is a deal breaker in this case because just keeping one version of each product is close to an unmanageable task for many store owners.
The disadvantage to using a custom database is that you really need to know what you're doing to achieve the required performance from the database and you will be spending a lot of time building the management UIs for the catalog; again a non-trivial task if you're dealing with fully customizable product template like we do in uCommerce with our product definitions. In essence all UIs become a matter of reflecting over each type of product and generating a fitting UI for that particular type.
However in the long run I believe (naturally :)) that this is the way to go because the end result will have far more flexibility and will be able to grow with the business as needed.
A detail which becomes a lot easier to deal with is installation. The primary integration between uCommerce and Umbraco is done on the UI level (for the management trees) and in a few instances on the data level: Integration with members, media library, and so forth. The advantage here is that we deal with upgrade scenarios only for our own stuff. Say we need to migrate the database from version 1 to 2; we only really need to deal with our own database. I cringe at the though of having to do the same thing for document types and content nodes holding the products.
We use nodes in SuperSimpleWebshop. We are using tags to accomplish cross-categorisation. This way the whole commerce section works exactly as anything else in Umbraco. For the target market of SuperSimpleWebshop this works, without doubt, the best. (it's not meant for large shops!)
We do some very nice tricks with the Umbraco API, to keep SuperSimpleWebshop as integrated with Umbraco as possible. This is more work than custom tables, but in the end it's a much more versatile solution.
Wow great responses and good arguments for both SuperSimpleWebshop and uCommerce :). Now this is a discussion!
is working on a reply...
This forum is in read-only mode while we transition to the new forum.
You can continue this topic on the new forum by tapping the "Continue discussion" link below.