integration with existing DB

[bdemenil]

I manage a site that runs on a fairly complex php/mysql product catalog:

www.putumayo.com

 

We are currently using a 3rd party to handle our shopping cart, but are researching ways to bring the shopping cart in house. How easy it would be to integrate with osCommerce. I'd basically like osCommerce to run off of our existing database rather than vice versa. Any advice?

[wizardsandwars]

it woulnd't be easy. And probably not really feasible.

 

The trick is, unless you want to completly re-write OSC, you still have to have all of the same columns that the OSC database tables have. Once you create all of the new column, into your current tables, then you'd have to match up which of your columns best matches up with the column from OSC. Then you'd have to find a way to link your current column to the cooresponding OSC columns. In a real DBMS, I'd say that you could use triggers to do this. But in MySQL, you'd have to create little scripts that constantly ran to update one set of column or anohter.

 

Personally, I'm a database programmer by profession, and to do somethign like this, I'd probably charge in the neighborhood of $2000 to do it. Not that I'd want to, just giving you an idea of what you're up against.

[Jeremy at oddly enough]

What do you have, about 150 titles or so? 16 categories with about ten titles per? I just had a quick glance at the site, to get an approximation. Then each title would have a pair of attributes to give the option between cd or cassette. You're looking at around 1100 mysql commands to put your catalog into the osc database. Someone who knew what they were doing could do that fairly quickly, especially if they had a dump of your current database to work from. The challenge I suspect is not in your catalog, but in your crm data about customers and distributors etc. . Undoubtedly a lot more info there since you have a 4000 member network of record stores. Still a clever person could do a lot with some raw data. I came up with scripts to load in the 1500 product catalog for someone who contacted me through these forums in a couple of nights. Took the fellow about 15 minutes to load it all in. I think it's probably a lot cheaper to pay a person to interpret and transfer the data once than to pay a person to figure out a way to make a computer do it continuously. Jeremy

[wizardsandwars]

I don't understnad what you mean.

 

In order for the databases to stay in sync, there would have to be a TON of work done. Tere are several ways to accomplish it, either syncronizing incremntally, or by complete refresh, but in some fashion, each query from one or the other systme would need to be duplicated.

[Guest]

If you can export your old database into a spreadsheet, it's be relatively easy to sort it out and add it into the catalog using the EasyPopulate contribution - it worked a treat for me and saved a lot of retyping!

[Jeremy at oddly enough]

That's what I mean. If you dump the existing database into a spread sheet, a person who knows the osc database will be able to transfer the data fairly efficiently, or prepare scripts so you don't need to grant direct access to your database. Jeremy

[Jeremy at oddly enough]

Somebody who has a good grasp of the osc dtabase would also recognize which data (if any) would need new fields or tables added to the database, and where they ought to go. Jeremy

 

As a related aside, one of my current projects is to transfer the catalog of a natural food distributor into osc, so that our food co-op might be able to use it more efficiently than the web-boards they're currently using for posting prospective orders to. There are about 3500 products, with 666 categories and subcategories, but it should only take a few days to convert it to the form that osc will use. The trick is going to be to include ther ability to share cases of a certain product between 2 or more members, but I could incorporate a bb to provide a forum for people to sign up for partial cases, and order the full case as they are taken.

[bdemenil]

The reason I want to stick with the current DB is that there is a fair amount of customization which might be difficult to add to osCommerce.

 

For example: CD itemtypes, for instance, have associated songs (stored in a separate table). Also, while items in a category are ordered by release date, that ordering can be over-ridden on any particular item - without disrupting the ordering of either the rest of the items in that category or of the ordering of that item in other categories.

 

The way the site currently works is that the shopping cart is hosted by a 3rd party. We have to coordinate with them so that they add new items to their DB as they are released. I guess, in the worst case, we could continue to do something similar, but with the shopping cart inhouse. Another way would be to add additional queries so that the osCommerce DB is updated anytime our native DB is updated.

 

A third way would be to hybrid the two - export our main items data into the osCommerce table, add the necessary fields, change our scripts to adapt to the new field names, and continue to use our code to display our catalog, but use osCommerce code to handle the shopping cart. This strikes me as the best solution, though it would take some time to implement. Does this sound feasible to you?

 

If wizardsandwars or anybody else is interested, this is something we might contract out.

 

Ben

[jrwrestling]

Chris is right in the fact that it is more difficult than it sounds, but it can be done.

[jpf]

Acutally if you have an existing MYSQL database with existing customer info---(with not the correct table names BUT if it has the same feild names) - by changing database_tables.php to match....

 

If the feild names don't match then it is not as easy....

[bdemenil]

I could match the fieldnames - I don't think it would be too much trouble.