Hi all
I am totally new to nopCommerce, and really have no idea how difficult it will be to do the things I want to do. We have an existing ASP.NET (web forms) site that we want to convert/integrate with nopCommerce to get all the ecommerce and other functions offered, and for future improvement in ease of expansion and maintenance. The existing site queries an SQL Server 2005 database using a load of pass-through SQL and stored procedures (and a few functions). The existing database reflects the inventory state of three physical stores (merge replication keeping them in sync.). Any site has to be tied into this database as it currently exists. Would it be possible to 'add' the nopCommerce database to the existing one (ie the tables, views, etc.), and then have nopCommerce use the existing tables (using the existing logic, which I know may have to be tweaked to fit the MVC approach), with operations that involved inventory? If this can't be done, and I need to somehow transform the current database into one that nopCommerce can work with, then I think I will have to find another approach; the existing logic queries a lot of different tables, so it wouldn't just be a matter of replicating data between an existing inventory table and the nopCommerce one - it would be much more complicated than that.
Thanks in advance for your opinions on the feasibility of this.
John
Integrating existing SQL Server 2005 database with nopCommerce
- 439
- 3619
- 29.07.2010
- Italy
Hi John,
I suggest you some guidelines. My work regards ERPs implementation and management and account systems, so I face these problems quite often.
1) Keep NOP db on its own. This allows release upgrades without pain, and, in future, you can switch to any other platform if you need.
2) Use an asynchronous update and synchronization. Bear in mind that query DBs every clock tick takes time and slows down performance dramatically. Timing depends from many factors, hourly, broadly speaking, is a good starting point.
3) If you have data and many users involved, it's much more efficient to setup store procs that build temporary tables rather then complex views and updates. Further advantage of this technique is that you keep separate the logic of each system: in this way it's easier to update procedures as releases (and needs) change. Keeping things easy is always the winning choice.
4) NOP DB is not as difficult to manage.
Hope to be of any help.
I suggest you some guidelines. My work regards ERPs implementation and management and account systems, so I face these problems quite often.
1) Keep NOP db on its own. This allows release upgrades without pain, and, in future, you can switch to any other platform if you need.
2) Use an asynchronous update and synchronization. Bear in mind that query DBs every clock tick takes time and slows down performance dramatically. Timing depends from many factors, hourly, broadly speaking, is a good starting point.
3) If you have data and many users involved, it's much more efficient to setup store procs that build temporary tables rather then complex views and updates. Further advantage of this technique is that you keep separate the logic of each system: in this way it's easier to update procedures as releases (and needs) change. Keeping things easy is always the winning choice.
4) NOP DB is not as difficult to manage.
Hope to be of any help.
- 4
- 20
- 31.05.2012
- United States
Thanks for the suggestions so far - I will certainly be keeping them in mind. Both databases will indeed be SQL Server (2005 in this case), and I will follow the advice to keep them separate and issue asynchronous updates from the existing one to the nopCommerce instance. My concern is exactly how I could update all the relevant info from one to the other, in that I would need several tables in nopCommerce that would at least partly reflect the structure of my existing tables. Is it possible to change the structure of the existing tables in nopCommerce (the ones that are created at installation), and add new ones that are needed? All the PK's in the existing database are GUIDs - would this cause problems, given that nopCommerce PK's are int identity fields? Plus, at the moment, the business does not do mail-order - all orders are picked up at one of the three stores - when a user first accesses the site, they select a 'home' store, and that store is given priority as far as choosing items (ie if the same item exists in more than one store, the 'home' store item will be selected for purchase, to minimize physical transferring of items between stores) - is in-store pickup currently handled by nopCommerce? We would certainly be moving to mail order at some point (one of the reasons to move to nopCommerce), but we would also continue the option of store pickup.
Sorry for so many questions. As I said, I'm very new to nopCommerce, and also to ASP.NET MVC (although I find the framework very interesting and am keen to learn it). Right now I am trying to get an idea of (1) if nopCommerce is a good fit for what we want to do, and (2) how much effort is likely to required by a nopCommerce, MVC-noobie to get the task done. I know that (2) is almost impossible to answer without knowing the full scope of the job, but an opinion of relative difficulty would be welcome. Management is keen to move to nopCommerce (or something similar) before too long, but obviously wants to know how much work that would be likely to involve. Your answers will help me to give a (perhaps) realistic estimate.
John
Sorry for so many questions. As I said, I'm very new to nopCommerce, and also to ASP.NET MVC (although I find the framework very interesting and am keen to learn it). Right now I am trying to get an idea of (1) if nopCommerce is a good fit for what we want to do, and (2) how much effort is likely to required by a nopCommerce, MVC-noobie to get the task done. I know that (2) is almost impossible to answer without knowing the full scope of the job, but an opinion of relative difficulty would be welcome. Management is keen to move to nopCommerce (or something similar) before too long, but obviously wants to know how much work that would be likely to involve. Your answers will help me to give a (perhaps) realistic estimate.
John
- 439
- 3619
- 29.07.2010
- Italy
Hi John,
I haven't your scenario so clear, but I try to clear your mind.
First of all, it isn't impossible to change database structure of any ERP or CRM, unless you redesign data layout completely. In other words, it's easier to build your custom system.
What you can do is to improve tables, adding custom fields (only if strictly necessary) or tables. You can add as many tables, views, store procedures, triggers as you need, they won't interphere with underlying software.
Second, don't change IDs. You can very easily add a cross table to yoin NOP IDs with your GUIDs, that's ok. It's rather boring but it worhks fine. Elsewhere (see above) you can add a GUIDs to NOP table, if you are sure that there's a one-to-one unique combination.
Next, I'm not sure to understand what you mean with "pickup" or "mail". NOP is a cart B2C sw, which means that to manage an order requires a long navigation experience. If you need a "pickup" then you need a shop sw, much easier to use just because the user is the shopkeeper.
What do you mean with "mail"? If you mean an automation process that imports mail orders coming from some specific sw, well... you need a specific andexpensive custom sw.
You say that you run three shops. What kind of shops? ecommerce, physical or both? Do you have only one warehouse? Besides, you want nop to manage your stocks. What kind of goods do you sell? Besides, what do you intend to do with NOP exactly?
I haven't your scenario so clear, but I try to clear your mind.
First of all, it isn't impossible to change database structure of any ERP or CRM, unless you redesign data layout completely. In other words, it's easier to build your custom system.
What you can do is to improve tables, adding custom fields (only if strictly necessary) or tables. You can add as many tables, views, store procedures, triggers as you need, they won't interphere with underlying software.
Second, don't change IDs. You can very easily add a cross table to yoin NOP IDs with your GUIDs, that's ok. It's rather boring but it worhks fine. Elsewhere (see above) you can add a GUIDs to NOP table, if you are sure that there's a one-to-one unique combination.
Next, I'm not sure to understand what you mean with "pickup" or "mail". NOP is a cart B2C sw, which means that to manage an order requires a long navigation experience. If you need a "pickup" then you need a shop sw, much easier to use just because the user is the shopkeeper.
What do you mean with "mail"? If you mean an automation process that imports mail orders coming from some specific sw, well... you need a specific andexpensive custom sw.
You say that you run three shops. What kind of shops? ecommerce, physical or both? Do you have only one warehouse? Besides, you want nop to manage your stocks. What kind of goods do you sell? Besides, what do you intend to do with NOP exactly?
- 4
- 20
- 31.05.2012
- United States
infinito62 wrote:
Hi Riccardo
Thanks for taking the time to try to understand my situation - it is very much appreciated.
I will try to clear up how things are done right now. We have three physical stores, which sell mainly used product (CD's, DVD's, Games mostly) which we buy from people who come into the stores. We also sell new product, which is mainly ordered from one large distributor, but this is a smaller part of the business. At the moment, people can search the website for items that are in any of the three stores, and place orders for any of these items. They can also order new product that isn't in any store, that will be ordered specially from the distributor. Whatever they order, they have to physically go to a store to pick it up - this is what I mean by 'pickup' - items are not sent out to anyone. When a user starts searching on the site, they select one of the three stores as their 'home' store, and they can search for items that are only in that store (which they can pick up immediately), or for items in any of the stores, for which there may be a delay in availability for pickup while the item is physically transferred from another store to their 'home' store. Customers make payments when they pick the order up - currently we don't take payments on the site. When I use the term 'mail,' I mean that items would be mailed (shipped) to the customer, rather than them having to come to a store to pick them up. Moving to nopCommerce would be mainly (at least at the beginning) to be able to use all its functions that are associated with selling and shipping items, such as shipping charges, tax, taking payments, etc. It would also (probably later) be to make customer management and contact, blog posts, etc. easier, along with easier maintainability. When we move to shipping items, we will still want to have the store pickup option, as many customers (practically all of them at the moment) are local, and many would still want to come in to a store to collect their orders.
I hope the above has made things clearer as to what we do and what we want to do. Please let me know if you think that we're on the wrong path thinking that nopCommerce could be a good fit for us. If so, and you know of a better alternative (preferably written in .NET, as that's what I'm most comfortable with), then please point me in that direction. I will very much owe you a beer the next time I am in Italy :)
Hi John,
I haven't your scenario so clear, but I try to clear your mind.
First of all, it isn't impossible to change database structure of any ERP or CRM, unless you redesign data layout completely. In other words, it's easier to build your custom system.
What you can do is to improve tables, adding custom fields (only if strictly necessary) or tables. You can add as many tables, views, store procedures, triggers as you need, they won't interphere with underlying software.
Second, don't change IDs. You can very easily add a cross table to yoin NOP IDs with your GUIDs, that's ok. It's rather boring but it worhks fine. Elsewhere (see above) you can add a GUIDs to NOP table, if you are sure that there's a one-to-one unique combination.
Next, I'm not sure to understand what you mean with "pickup" or "mail". NOP is a cart B2C sw, which means that to manage an order requires a long navigation experience. If you need a "pickup" then you need a shop sw, much easier to use just because the user is the shopkeeper.
What do you mean with "mail"? If you mean an automation process that imports mail orders coming from some specific sw, well... you need a specific andexpensive custom sw.
You say that you run three shops. What kind of shops? ecommerce, physical or both? Do you have only one warehouse? Besides, you want nop to manage your stocks. What kind of goods do you sell? Besides, what do you intend to do with NOP exactly?
I haven't your scenario so clear, but I try to clear your mind.
First of all, it isn't impossible to change database structure of any ERP or CRM, unless you redesign data layout completely. In other words, it's easier to build your custom system.
What you can do is to improve tables, adding custom fields (only if strictly necessary) or tables. You can add as many tables, views, store procedures, triggers as you need, they won't interphere with underlying software.
Second, don't change IDs. You can very easily add a cross table to yoin NOP IDs with your GUIDs, that's ok. It's rather boring but it worhks fine. Elsewhere (see above) you can add a GUIDs to NOP table, if you are sure that there's a one-to-one unique combination.
Next, I'm not sure to understand what you mean with "pickup" or "mail". NOP is a cart B2C sw, which means that to manage an order requires a long navigation experience. If you need a "pickup" then you need a shop sw, much easier to use just because the user is the shopkeeper.
What do you mean with "mail"? If you mean an automation process that imports mail orders coming from some specific sw, well... you need a specific andexpensive custom sw.
You say that you run three shops. What kind of shops? ecommerce, physical or both? Do you have only one warehouse? Besides, you want nop to manage your stocks. What kind of goods do you sell? Besides, what do you intend to do with NOP exactly?
Hi Riccardo
Thanks for taking the time to try to understand my situation - it is very much appreciated.
I will try to clear up how things are done right now. We have three physical stores, which sell mainly used product (CD's, DVD's, Games mostly) which we buy from people who come into the stores. We also sell new product, which is mainly ordered from one large distributor, but this is a smaller part of the business. At the moment, people can search the website for items that are in any of the three stores, and place orders for any of these items. They can also order new product that isn't in any store, that will be ordered specially from the distributor. Whatever they order, they have to physically go to a store to pick it up - this is what I mean by 'pickup' - items are not sent out to anyone. When a user starts searching on the site, they select one of the three stores as their 'home' store, and they can search for items that are only in that store (which they can pick up immediately), or for items in any of the stores, for which there may be a delay in availability for pickup while the item is physically transferred from another store to their 'home' store. Customers make payments when they pick the order up - currently we don't take payments on the site. When I use the term 'mail,' I mean that items would be mailed (shipped) to the customer, rather than them having to come to a store to pick them up. Moving to nopCommerce would be mainly (at least at the beginning) to be able to use all its functions that are associated with selling and shipping items, such as shipping charges, tax, taking payments, etc. It would also (probably later) be to make customer management and contact, blog posts, etc. easier, along with easier maintainability. When we move to shipping items, we will still want to have the store pickup option, as many customers (practically all of them at the moment) are local, and many would still want to come in to a store to collect their orders.
I hope the above has made things clearer as to what we do and what we want to do. Please let me know if you think that we're on the wrong path thinking that nopCommerce could be a good fit for us. If so, and you know of a better alternative (preferably written in .NET, as that's what I'm most comfortable with), then please point me in that direction. I will very much owe you a beer the next time I am in Italy :)
- 439
- 3619
- 29.07.2010
- Italy
Hi John,
now everything's clear. A friend of mine have, here in Milan, the same activity of yours, selleing, also, PCs, HI-FIs and so on. He doesn't sell online because the strengh of the business is that people come in and carries things to sell - while buying some other articles.
Anyway, the solution is not as simple as you hope...
1) You need an ERP that has 4 selling units (3 shops and an ecommerce). Each shop has its own warehouse while the B2C has the virtual derived by the sum of all physical
2) Shops have to be conencted to the ERP, so that the stocks are always inline, both when selling and buying. In this way you're always up - also (and this is very important) with cash flow
3) NopCommerce doesn't manage multistore while manages multiwarehouses. But this is not of greast importance: it has to show instock articles, prices (I guess) and so on. When customers checks out, the only choice is "in stock payment" and "in shop pick up". Here some NOP customization is needed, to taylor the shop selection.
4) At data layer, the best solution is the one I proposed - update stocks hourly or so. As you don't sell online, you don't have tyo track payments, so this process is not so difficult
5) What have to be done from scratch is delivery / pick up time schedule. This process needs:
a) Cross internal order process to automate transfer between shops: picking lists, cross stock updates, etc.
b) Notification to the customer, who's know when to go to the shop to pickup his goods
c) Again, reversal stock adjustments when products have been loaded in the destination shop
d) Again, notification to the customer that his articoles are ready to pickup (or have been delivered by post / FED, etc.)
This broadly speaking. You have to figureout how your ERP manages this process.
I sell these solutions, so it's perfectly clear to me... if you're interested I run ERP in server farm and shops can be connected via Internet... but our solutions are professional, not open source like NOP...
For any private question, please PM, if you need further info just post.
now everything's clear. A friend of mine have, here in Milan, the same activity of yours, selleing, also, PCs, HI-FIs and so on. He doesn't sell online because the strengh of the business is that people come in and carries things to sell - while buying some other articles.
Anyway, the solution is not as simple as you hope...
1) You need an ERP that has 4 selling units (3 shops and an ecommerce). Each shop has its own warehouse while the B2C has the virtual derived by the sum of all physical
2) Shops have to be conencted to the ERP, so that the stocks are always inline, both when selling and buying. In this way you're always up - also (and this is very important) with cash flow
3) NopCommerce doesn't manage multistore while manages multiwarehouses. But this is not of greast importance: it has to show instock articles, prices (I guess) and so on. When customers checks out, the only choice is "in stock payment" and "in shop pick up". Here some NOP customization is needed, to taylor the shop selection.
4) At data layer, the best solution is the one I proposed - update stocks hourly or so. As you don't sell online, you don't have tyo track payments, so this process is not so difficult
5) What have to be done from scratch is delivery / pick up time schedule. This process needs:
a) Cross internal order process to automate transfer between shops: picking lists, cross stock updates, etc.
b) Notification to the customer, who's know when to go to the shop to pickup his goods
c) Again, reversal stock adjustments when products have been loaded in the destination shop
d) Again, notification to the customer that his articoles are ready to pickup (or have been delivered by post / FED, etc.)
This broadly speaking. You have to figureout how your ERP manages this process.
I sell these solutions, so it's perfectly clear to me... if you're interested I run ERP in server farm and shops can be connected via Internet... but our solutions are professional, not open source like NOP...
For any private question, please PM, if you need further info just post.
- 4
- 20
- 31.05.2012
- United States
infinito62 wrote:
Hi Riccardo
Thanks again for so much helpful information. From what you have told me so far, I think that nopCommerce can work for us, but it will need quite a lot of customisation. That's probably normal when dealing with anything but the simplest implementation, and especially so when it has to interface with existing systems such as we have. A lot of the functions you describe above (notifying customers, scheduling transfers, etc.) are already done by the existing in-store(s) system, and would probably continue to be after nopCommerce was integrated, as the store staff are comfortable with that approach, and it has been working well so far. I have to wait to see if the management wants to go ahead with this or not - if so, it will probably be a matter of months before we do it; I will use that time to learn more about nopCommerce and the changes I will need to make. Your input has been invaluable to the decision process so far, and I will certainly PM you if more questions come up - thanks for offering that. Thank you again!
Hi John,
now everything's clear. A friend of mine have, here in Milan, the same activity of yours, selleing, also, PCs, HI-FIs and so on. He doesn't sell online because the strengh of the business is that people come in and carries things to sell - while buying some other articles.
Anyway, the solution is not as simple as you hope...
1) You need an ERP that has 4 selling units (3 shops and an ecommerce). Each shop has its own warehouse while the B2C has the virtual derived by the sum of all physical
2) Shops have to be conencted to the ERP, so that the stocks are always inline, both when selling and buying. In this way you're always up - also (and this is very important) with cash flow
3) NopCommerce doesn't manage multistore while manages multiwarehouses. But this is not of greast importance: it has to show instock articles, prices (I guess) and so on. When customers checks out, the only choice is "in stock payment" and "in shop pick up". Here some NOP customization is needed, to taylor the shop selection.
4) At data layer, the best solution is the one I proposed - update stocks hourly or so. As you don't sell online, you don't have tyo track payments, so this process is not so difficult
5) What have to be done from scratch is delivery / pick up time schedule. This process needs:
a) Cross internal order process to automate transfer between shops: picking lists, cross stock updates, etc.
b) Notification to the customer, who's know when to go to the shop to pickup his goods
c) Again, reversal stock adjustments when products have been loaded in the destination shop
d) Again, notification to the customer that his articoles are ready to pickup (or have been delivered by post / FED, etc.)
This broadly speaking. You have to figureout how your ERP manages this process.
I sell these solutions, so it's perfectly clear to me... if you're interested I run ERP in server farm and shops can be connected via Internet... but our solutions are professional, not open source like NOP...
For any private question, please PM, if you need further info just post.
now everything's clear. A friend of mine have, here in Milan, the same activity of yours, selleing, also, PCs, HI-FIs and so on. He doesn't sell online because the strengh of the business is that people come in and carries things to sell - while buying some other articles.
Anyway, the solution is not as simple as you hope...
1) You need an ERP that has 4 selling units (3 shops and an ecommerce). Each shop has its own warehouse while the B2C has the virtual derived by the sum of all physical
2) Shops have to be conencted to the ERP, so that the stocks are always inline, both when selling and buying. In this way you're always up - also (and this is very important) with cash flow
3) NopCommerce doesn't manage multistore while manages multiwarehouses. But this is not of greast importance: it has to show instock articles, prices (I guess) and so on. When customers checks out, the only choice is "in stock payment" and "in shop pick up". Here some NOP customization is needed, to taylor the shop selection.
4) At data layer, the best solution is the one I proposed - update stocks hourly or so. As you don't sell online, you don't have tyo track payments, so this process is not so difficult
5) What have to be done from scratch is delivery / pick up time schedule. This process needs:
a) Cross internal order process to automate transfer between shops: picking lists, cross stock updates, etc.
b) Notification to the customer, who's know when to go to the shop to pickup his goods
c) Again, reversal stock adjustments when products have been loaded in the destination shop
d) Again, notification to the customer that his articoles are ready to pickup (or have been delivered by post / FED, etc.)
This broadly speaking. You have to figureout how your ERP manages this process.
I sell these solutions, so it's perfectly clear to me... if you're interested I run ERP in server farm and shops can be connected via Internet... but our solutions are professional, not open source like NOP...
For any private question, please PM, if you need further info just post.
Hi Riccardo
Thanks again for so much helpful information. From what you have told me so far, I think that nopCommerce can work for us, but it will need quite a lot of customisation. That's probably normal when dealing with anything but the simplest implementation, and especially so when it has to interface with existing systems such as we have. A lot of the functions you describe above (notifying customers, scheduling transfers, etc.) are already done by the existing in-store(s) system, and would probably continue to be after nopCommerce was integrated, as the store staff are comfortable with that approach, and it has been working well so far. I have to wait to see if the management wants to go ahead with this or not - if so, it will probably be a matter of months before we do it; I will use that time to learn more about nopCommerce and the changes I will need to make. Your input has been invaluable to the decision process so far, and I will certainly PM you if more questions come up - thanks for offering that. Thank you again!