One database, multiple sites - shopping cart-ish system

[versatilewt]

I'm working on a project where there are two different web sites that share many similar items in a catalog. One web site is for the industry professionals, and the other is directed towards consumers and end users more. What I would ultimately like to do is have them share the same products tables, with different pricing levels available for each web site, and the possibility for a product to show up on one web site but not both.

 

Any suggestions?

[redbullmarky]

personally, unless it's too much trouble to enter product info twice, i'd advise against a shared DB.

 

however, if you have to, then you'd probably be looking at seperating your pricing from the items into a table of its own, and giving all items/prices a 'site' id - something unique for each site.

 

i have done it before - but my decision was based on the DB's having more in common than not. some of my reasons:

 

a) same logs/sessions (i keep all my site requests/sessions in the DB)

b) i used a single administration panel for both sites, as it was all recruitment based.

c) both sites on the same server

d) both sites share same DB structure

 

if something was unique to a site, then the 'site' field for a record would be set to say 'siteA' or 'siteB'. if it was for both sites, then i'd leave it empty.

 

there are advantages to having it all in one DB, mainly the fact that changing content/structure in one DB for one site would also do the same for the other, but i found it more efficient to keep them seperate.

[utexas_pjm]

if something was unique to a site, then the 'site' field for a record would be set to say 'siteA' or 'siteB'. if it was for both sites, then i'd leave it empty.

 

I'd suggest a slightly more scalable approach:

 

Schema:

 

items { item_id, item_name, item_description, item_image }

sites { site_id, site_name }

prices { price_id, item_id, site_id, price }

 

Now, to get all of the items and prices for site 1 we can execute the following query:

 

SELECT i.item_name, i.item_description, i.item_image, p.price
FROM prices p LEFT JOIN items i USING (item_id)
WHERE p.site_id = 1

 

By using an outer join we are limiting the results to only items which have a price defined for the site specified in the where clause.  Hope this helps.

 

Best,

 

Patrick

[roopurt18]

I don't know how feasible this is for you, but I think it's really important to keep data separate when it actually is separate.

 

Essentially you have 3 major sections of data.

 

1) Product Information

 

2) Site 1 Product Pricing

 

3) Site 2 Product Pricing

 

If you're really hellbent on keeping the two sites separate, I'd create three databases.  One for each site, including users, pricing, whatever else, and the third to just contain product information.  This has the advantage of enabling each site to override the values contained in the general product database without polluting the product database with information that has nothing to do with products.

 

 

Let's look at an example product:

Name: widget

Description: foobar

Price: 1.00

 

The db.table to store that information:

products.prod_info { id, name, description, price }

 

Then each site can also contain a prod_info table that overrides any values in product.prod_info as well as add new values, such as quantities, consumer ratings, etc.

site1.prod_info{ id, main_prod_id, name, description, price, qty }

 

Then in your site 1 code, when you wish to query for a product:

SELECT

  IFNULL(s.name, p.name) AS Name,

  IFNULL(s.description, p.description) AS Description,

  IFNULL(s.price, p.price) AS Price,

  IFNULL(s.qty, 0) AS Qty

FROM products.prod_info p

LEFT JOIN site1.prod_info s ON (p.id = s.main_prod_id)

WHERE

  filtering

 

 

I've never tried to join across multiple databases so I don't even know if that approach is possible, but if it were I think it's the one I'd go for.  It has the hassle of a little extra typing in your queries, but it keeps the data where it logically belongs IMO.

 

Of course, the other approach is to not create two sites at all.  Just make a single one and base what page people view based off the URL, login credentials, etc.  It sounds like this is an existing project though, so that may not be possible.

[utexas_pjm]

I've never tried to join across multiple databases so I don't even know if that approach is possible, but if it were I think it's the one I'd go for.  It has the hassle of a little extra typing in your queries, but it keeps the data where it logically belongs IMO.

 

In MySQL it is possible granted that the user has the appropriate privileges on both schemas.

[roopurt18]

That's good to know.

 

I'm curious as to what you other folks think about my proposed design.

[QWERTYtech]

Can anyone give me a good site for reference if I'm wanting to create my own shopping cart?