Database Access? On site or off?

Anon

Hey guys/gals I've got a small client who wants to integrate his website with his inventory control system. I can do the integration, but the question is if the main inventory database should be onsite (with the website accessing data through a cable connection) or offsite (with the store accessing data on a server elsewhere over the cable connection).

I was hoping someone else has run into the question and had some insights.

Thanks,
Devin Pittman

Anon

I'm a database newbie so it seems ok to have a database served up local to the machine as long as it's bound to 127.0.0.1 and not to your general internet IP. If you can telnet to it from anywhere on the net that could be less security than you need... A firewall should block this though.

Anon

I'm not sure you quite got what I was asking. I need to be able to access the database from both the store and the client's website.

I've got two options:

Locate the database in a local server farm (offsite) with high bandwith so the website can access it very quickly, thereby requiring the store terminals to receive the data over a cable internet connection to the offsite server farm.

Locate the database on a server in the store so that the store systems access the data quickly, thereby requiring the website to connect to the onsite server via a cable connection to request inventory data.

[deleted]

Neither of the two is an option, because you depend on a connectione between the two.
If that connection fails (and it will, murphy says so) then one of the apps fails.
Most likely you'll want the internet store to fail, because people understand that a lot better than when the salesperson behind the desk says "sorry, I can't sell you this item you have in your hand because the internet is down" 🙂

What you'll end up with is some sort of two-way replication, where updates from the store are replicated to the web, and updates from the web are replicated to the store.
That way, in case of a loss of connection both shops can continue to run. When the connection is restored both databases will be syncronised again.

The only issue is, what do you do about stock control etc when you are selling products on two sites and they temporarily get out of sync.

There will never be a good solution for that, but I you could make it so that when the connection is lost, the webshop switched from making live-sales to making reservations, which are processed when the connectionis restored and the databases are in sync.

Anon

thanks vincent! You're suggestion was exactly what I thought, but I didn't want to have to do that much work. Anyhow I guess thats the approach I'll take.

Thanks Again

Devin

Anon

Devin,

Funny you asked this, I am looking for exactly the same answer as you were. Did you come up with a solution for this?

I am in a situation where a company has an inventory system connected to their local database. I need to make an ecommerce site, and it would be IDEAL if I could have the web site AND the inventory system connecting to the LIVE database...

Any help would be greatly appreciated.

Regards,
Adam

Anon

Adam,

I think I might be close to solving this problem, at least in my case. I found an article on MySQL database replication here on phpBuilder and it's helped a lot. I would suggest taking a look at it.

As vincent suggested I will be running a MySQL database on the webserver as well as in the local shop. The local MySQL database will act as a master database and the webserver will act as a slave database. This means that the slave will update itself from the master and incorporate the changes into itself. The system then will be reversed so that the webserver also acts as a master and the local server acts as a slave. This way any changes in the webserver database will be automatically be sent to the local database. By doing this I will have some redundancy in the systems. If the webserver database goes down the store can operate normally and vice versa.

I know that sounds a bit complicated and I'm not even sure that it will work, but I'm going to test that out. I do see one very big problem with the above method though - data throughput. I think that the amount of data changing on a minute to minute basis will be too much to keep transmitting to the website. So the next paragraph explains my second alternative.

The website really only needs to know how much stock is in the store and not all the specific store related details of each and every product. So, I figure that I'll make a master/slave relationship in the store, with two databases running on the one server, but with the slave only updating the inventory amount of each item, not all the particulars. Then, I'll setup a master/slave relationship with the webserver to ensure that inventory amounts are kept up to date online and vice versa. This way the amount of data will be signifigantly minimized, yet will acheive the desired result of a combined inventory system.

I hope that makes sense and helps somewhat. If you've got any questions just post them and I'll try to explain it a bit better.

Devin

[deleted]

Devin,

about that last bit, the updating of quantities only. Won't you be listing product details on the website? Shouldn't they be up to date too?

Anyway, MySQL supports compressed data connections, which will greatly reduce the amount of data that is actually sent over the network.

And don't forget to build some feature that can check wether the database is still being replcated. If the shop database is leading, then the web database must know if it is up-to-date, and it must stop making changes in the database when the databases are out of sync (the network connection is lost) or you will have a problem when the replication kicks in again. (both 'shops' may have sold the last item in stock)

Another thing you should definately investigate is security. The latest versions of MySQL support encrypted data connections, making your data safe from snoopers and eavesdroppers.