One database for two websites

I have a live working Zencart site and would like to have a sandbox site that uses the same database. can that be done? If so, I would appreciate any guidelines on how to implement it. thanks

No, it can't be done. Any changes you make to the database in test (selecting a new template, adding categories/products, etc) will be reflected in the live store.

If you are restricted to one database, you should be able to give your test site's tables a different prefix, which should solve the problem I mentioned above.

Mary Ellen is correct...

As a huge number of shop parameters are controlled in the database, having more than one site share a database (while possible) will mean that configurations applied to any one site, will also be applied to all the others using the same database.

So, your "sandbox" changes will also appear in your real store...

By using a different TABLE PREFIX, you can "house" data for a different application in any database.

So, for example, your LIVE STORE may have the tables prefixed by ZEN_ (a common prefix) or may mot have a prefix at all.

You could do a second install of ZC (in a sub folder of your main site) and in the process of installing, ensure that a PREFIX is given to the tables for that second install.

Much better is to create a full CLONE of the site, and have its dbase competely separate. (if your host is generous enought to allow more that one dbase)

Thank you both for very clear answers :)

:-)

Sorry for bumping this old thread, but I'm considering the same thing.

I want to share the product-information between two shops which carry the same items. MultiSite isn't an option, because I don't want to share the carts / customer info etc.

The idea is to create two shops in a single database, using two different table-prefixes, zen1_ and zen2_ and create tables for both stores.

After creating both stores, I'll drop the products tables from the zen2_ database, and make views to the zen1_ tables.

Since views work like tables (with some restrictions) this might actually work :-)

The plan looks almost too simple :) Anyone who tried this? Or anyone who sees any drawbacks?

The separate prefixes should work and keep the information separate, but you can't mix and match between them, so store1 will use zen1 and store2 will use zen2.

Yeah, but the idea is to share the zen1_products table using a view with the name zen2_products. So there isn;t actually a zen2_products table, but it's a view.

Any problems to be expected?

Zencart uses a complex relational database, so there are numerous occasions where data is cross-referenced. Data is drawn from tables and written to other tables during many customer procedures and the ways this is achieved is extremely detailed.

Here's just one challenge you will face, and is the first hurdle you will have to overcome.

What you propose (shared product data across two sites on the same database) will probably require a database with three distinct table prefixes... store-1, store-2, and shared. This is reasonbaly easy to create on a technical level, but the php CODE that manages the operations and functions within zencart will have to be completely re-written in order to accommodate this radical alteration to the database structure. Given that there are several hundred-thousand lines of code, it will be a busy procedure and could take you a good few months to achieve. (We'll ignore the cost as we assume you'll do it all yourself).

It would be easier to start writing zencart from scratch...

... err guys. It can be done, and it doesn't require oodles of re-writing of core.

For example:

1. Create two sites in the same database with different database prefixes, e.g. "zen1_" and "zen2_".

2. In the site pointing to "zen2_", edit the includes/database_tables.php file to change the following line> define('TABLE_PRODUCTS', DB_PREFIX . 'products');to> define('TABLE_PRODUCTS', 'zen1_products');This points the products table from site 2 to the products table for site 1.

3. IMPORTANT now do step 2 for all the other product and category-related pages (you could separate them out, but you would really have to know what you're doing).

Things to look out for (gotchas) ...

If you're using mods that introduce different product types or new tables that extend any of the existing product or category tables then you'll need also to update the declarations for those tables which should be in the includes/extra_datafiles folders in the catalog and admin, though sometimes get embedded at the top of the mods main file.

If you're using mods that hard-code the table names (naughty), then you will need to correct this in order for this approach to work.

Be aware that if this doesn't work on your site, it's going to be difficult for us to provide support, since the problem will almost certainly be specific to non-core code being used on your site, that we can't see. However, conversely, we're making very limited changes to a single file, so it would be very easy to reverse.

Thanks for the replies... I'll be looking in to that changing of the defines...

Although I still feel the most for using views... I can't see how the restrictions on views would affect the shop behaviour:

• It is not possible to create an index on a view.
• Indexes can be used for views processed using the merge algorithm. However, a view that is processed with the temptable algorithm is unable to take advantage of indexes on its underlying tables (although indexes can be used during generation of the temporary tables).

Subqueries cannot be used in the FROM clause of a view.

If the view thing works, no lines of code, even no defines, have to be changed...

If I understand correctly what you're proposing, it would make the store that was relying on views unbelievably slow.

Zen Cart frequently joins lots of tables together and the fields used to construct those joins have been carefully indexed to optimise response times.

To perform those joins on unindexed views would create an enormous performance hit.

Well, using the define option might work better then... maybe I'll try both, to see if there's any performance difference... but both solutions use the same approach... 'redirect' the data from one table to another.. :-)

RobotStore:

Well, using the define option might work better then... maybe I'll try both, to see if there's any performance difference... but both solutions use the same approach... 'redirect' the data from one table to another.. :-)Hmmm. They're not really the same at all. A view isn't a table. It's really the output of a saved query. So the view approach crudely equates to replacing each table in product-related Zen Cart query, with a subquery that needs to be linked by full text comparisons in the absence of any indexing - hence the performance hit.

This is one interesting thread indeed!

kuroi:

... err guys. It can be done, and it doesn't require oodles of re-writing of core.

For example:

1. Create two sites in the same database with different database prefixes, e.g. "zen1_" and "zen2_". .........

kuroi, how would one create two sites in the same database? I'd be very interested in reading the solution to this question.

Thanks / Frank

frank18:

This is one interesting thread indeed!

kuroi, how would one create two sites in the same database? I'd be very interested in reading the solution to this question.

Thanks / Frank

Safest way is to use the standard zencart INSTALL procedure for a new website. Point the install to the SAME database you are using for your existing site

When prompted (during setup), for a database prefix, put the desired prefix into the input field.

RobotStore:

Well, using the define option might work better then... maybe I'll try both, to see if there's any performance difference... but both solutions use the same approach... 'redirect' the data from one table to another.. :-)

They're not really the same.

Plus, in v1.x there is an architectural limitation which requires that you have write privileges ("UPDATE") to the table so stats can be updated every time a visitor visits a product's info page. If you're using a read-only view then you'll encounter SQL errors.
Instead, if you use the prefix approach suggested by kuroi you won't run into that problem.

@frank18

Schoolboy's approach would work fine, but there are some issues specific to how web hosts configure their hosting accounts that would need to be taken account off too.

For many hosting accounts, database access is treated as integral to the account. It doesn't have to be, but it's understandably convenient for the hosts to manage it thus. Unfortunately, many hosts can only allow you one dedicated IP address per account, and therefore only one dedicated SSL. So even if you have many sites running off one database, you may have to make provision for them to use the same SSL cert when they go live.

You also end up with two sets of code to patch and upgrade. Personally I prefer to run with one set of code and one database. There's the multisite mod for doing this, which is one approach. I use a different one.

The hidden wholesale mod was originally conceived off as a way of running wholesale and retail sites from a single installation. However, my version of it centralised the condition for deciding which products could be used for which site, which makes it incredibly flexible. For example I have a client with a couple of extra fields on the product table to indicate to which site each product belongs (or both). I have another with a dedicated sale site that filters on whether a product has a sale price.

In these cases there is also a tweak to config files to ensure that incoming URL is perpertuated, and an overridden init_templates file to ensure that the right template is used for each variant of the site.

Hope that provides extra food for thought and not indigestion. :D

@kuroi & schoolboy

so, if I understand that right, using the same account with 2 stores in 2 separate subdomains should work just fine as long as one uses the different prefixes? Have to give this one a go over the weekend on my local machine.

Thanks to all of you - time for beddies :wacko: