Your thoughts on multi-store DB design philosophy...

Hello and thanks in advance to all you wonderful people providing your time to help others.

I developed an Access DB around 10 years ago for my 3 shoe stores and it's been working great so far, however none of them are connected to each other, they are all separate machines running separate complete DB, so this is a problem as on receiving new inventory, each store has to enter products separately and I can not easily find total inventory counts.

Business is very slow nowadays and I've been thinking of making a shoe selling website to sell shoes online only in my own country, however this website also needs to connect to my DB so customers can choose the product and size they want, so below are my thoughts of the way I should plan and a total redesign of my DB and I wanted you to point out where I should start, what I should learn and how I should design my new DB, so I appreciate if you could find faults on the points below...


1- I'm thinking I need to have only ONE centralized DB, instead of the current system where each shop has it's own separate DB, like a back end containing only one main table for ALL my stores, located either on the web or on my own PC which will act like a server for all the stores and the website to draw data from. This way I can easily deal with just one DB containing all the tables and inventory for all the shops in one place.

2- with the above scenario, all the stores need to have Internet connection and a front end access program running which connect to the back end DB, am I correct on this? the website will also be like another store with front end DB connecting to the Back end?

3- what if one of these stores will be offline for a day or two due to some technical problem? does access allow full functionality of that front end system and data entry offline? and then sync those data with the main back-end once connection is available?


Sincerely,

Bobak.

Thank you so much Pat for your answer, English is not my first language, so forgive me for any blunders beforehand.

So the Gist of what I'm understanding from you is:

1- That separate DB's with all separate tables is a bad idea and a Centralized back end with all the stores connected to it is a much better idea, am I correct on this? if so then I will start learning Microsoft SQL server 2008 standard. since I'm starting from scratch, I will build my backend on that and host all the main tables there in one place, then all the branches will be running an ACCESS 2013 frontend and will link all the tables to the back end? is this the model I should stick to?

2- Do you recommend this book to start me of on the path of righteousness ...
http://www.amazon.com/Beginning-SQL-Server-2008-Developers/dp/1590599586


3- Regarding the connection issue, you mean to use programs like TightVNC and other free Desktop sharing apps?
they always seemed kind of finicky to me, but you are not referring that I should be sharing desktops right? you mean access can use that connection in other ways just to send data? sort of like an ODBC connection?

4- How about I keep all my front end DB's in an offline state, and all the sales just keep writing to a table and then at closing time an update query connects to the backend and runs the update query? is that the general roadmap?



I sincerely thank you again for your time and ideas.

Bobak.

Thanks again Pat, sorry for the long delay, in order not to waste your time any further, I'll just summarize and finalize.

1- That is exactly my plan for the future, an up to date stock table to support the website.

2- Thanks for stopping me from purchasing that book, I was REALLY not up to reading a 800 page book, so I will just make my BE using the normal Access 2013 SQL and be done with that.

3- I have to look into ways of Access connecting to other Access DB through the Internet, if you had to choose a free one, which one would you recommend, remember, it's not mission critical situations, if there's no link, my DB will try to connect again in 10 minutes time,

So when you have two DB in two separate locations, you just need to find a way to link the FE to the BE tables right?


The point you made on an online shopper needing to know if a product is in-stock was exactly my concern.

I have some reading to do, but you put me on the right path.

I thank you again.

Bobak.

Yes, I've already bought a hosting site for my future website and left it as is so I can learn all this stuff and then make my website, it's here...
http://www.amirashoes.com/

That sounds like a great idea, I'll have my main Back end DB hosted on my site and then all the front ends can link tables with the DB back end which is on the Internet. I can live with 10$ a month

Cheers.

I had a look at NetSupport program...
http://www.netsupportmanager.com/overview.asp

But since I'm going to be paying monthly for my website, I might as well do as you suggested and put my BE DB on my site and have all my FE's linking their tables to that, am I thinking right?

Thanks again.