Your thoughts on multi-store DB design philosophy... (1 Viewer)

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.

Once you need the internet to process an order, you will need to develop a fall-back option that you can use if one or more stores can't get on line. I might download a current inventory at close of business each day into a backup database. Then the backup cash register db would have only the order entry functions. It doesn't need the rest of the stuff. All you need to be able to do is record an order and perhaps answer inventory questions. Your main app will need to be able to take a "backup" app database and import the orders.

If you want to support web processing, it is best to switch from Jet/ACE to SQL server.

A simple solution to sharing among three locations is to use Terminal Services (Remote Desktop) or Citrix. You can buy a Citrix connection for $20 per user, per month if you don't have the resources to set one up yourself. It will probably take a couple of years to amortize the cost of setting up your own server, etc and as more companies enter the "cloud" sphere, prices will go down. The service bureau will take care of backups but you will have the ability to log on and do stuff yourself. This same service bureau would need to host your web app.

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.

1. I suggested a consolidated database only because you wanted to support orders from the web. If you are going to maintain a "web" inventory separate from what is in the stores, then separate databases for each location + the web would be fine.
2. If you use linked tables, you won't actually be using T-SQL. You'll be using the Access SQL variant and so technically don't have to learn anything new. In practice, there are things that you should do differently in your Access FE to take advantage of the client/server environment but you won't learn them from a book about SQL.
3. I would not suggest one of the free apps you mentioned. I would choose one intended for professional use that provided support.
4. That is fine but it means your web app won't have an accurate inventory.

Obviously, the simplest solution is separate databases for each location. The app would have some summarization tools for daily activity and at the end of the day or weekly, whatever, you would upload the activity for consolidated reporting. What throws a kicker into the mix is the web app. It is the web app that drove my suggestion. Personally, as an on-line shopper, I want to know that what I am ordering is in-stock. I don't want to place an order only to find out that the seller can't actually deliver. That's the point of the consolidated inventory. I presumed, you would ship from any location, where ever the stock was located.

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.

3. You can purchase database space at a hosting website. They can host Jet/ACE and SQL Server and usually MySQL also. You can connect directly from your Access FE to the hosted database but it will be a slow connection and you would not want to run your cash registers that way. You could use it for transferring data though.

I am not aware of anything Free that will work for you. Drop-box type technology will not work for this purpose. There are Free GoToMyPC like options and those would connect to a specific PC which could be your home computer. The database hosting I suggested above can be gotten for $5-10 per month. Some sites also charge for data transfers so make sure you understand what you are signing up for.

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.

You need to discuss this with your provider. Not all support connections from Access FEs but yes, if you can connect directly from Access, do it. I have found it to be too slow to be usable but you may have better luck.