How many databases to support eCommerce?

Question

I have a system with two databases, one that the customer-facing website uses, the second that is used by the "backroom" order-fulfillment system. I've been asked to run queries from the website to the backroom system. I'd rather not, it seems risky to allow web-based request to run unheeded on the internal system. Additionally, this means opening up routing in the firewall to allow external connections to the internal server.

What's the best practice for eCommerce? Run the entire company off of one database? Or individual databases for each system, and middleware to connect them?

Sometimes it might be necessary for the web application to pull date from the internal system, but not based on an HTTP request from the internet.

I'm sure the best answer is "it depends!" So, if people have a rule of thumb for when to use middleware and when not to, I'd like to here it.

Answer 1

I'd rather not, it seems risky to allow web-based request to run unheeded on the internal system.

Even less secure would be using a single database which clients connect to through an HTTP server. It is quite common to have several databases across different security zones.

Answer 2

Well you could replicate the data from your transactional databases to read-only copies to run reporting queries against as a relatively quick fix solution. With eCommerce however you need to be aware of PCI and ensure you're following their requirements to safeguard sensitive customer / credit card data. PCI Compliance Guide

The next step is to build a data warehouse that's organized specifically for reporting purposes - making it easier to write queries as well as have them run much more efficiently to slice and dice your data - seeing it from multiple points of view (dimensions). Ralph Kimball Data warehouse Guru

Answer 3

There are lots of options here.

1) Data shipping. You could send data up to the web database so it has it's own local cache of information to use when it needs to.

2) Services. You could protect your internal database using a service that accepts requests and sends data back. This avoids any direct access to your internal database but gives you the benefit of data that isn't out of data, as it would be in option 1.

3) Service Bus. You could stick some middleware in between the two systems that deals with things such as guaranteed delivery and ETL if you need those things.

Essentially, there are lots of right answers - and a few wrong answers...

Wrong answers include sticking everything in a single database, allowing direct access from the internet to your internal database and setting up a linked-server between the two databases!