Multiple customers/databases same structure

[Cowboy_BeBa]

Hey all, hows it going?

Ive recently started up my own company (with a partner who is in charge of sales/marketing) and have built an MRP system in access

My partner wants to switch over to an SQL backend/web frontend
I dont have much time to get the job done (as i also have a day job) so we're outsourcing to india to get the front end built and i just plan to convert the backend to sql and send the team in india the CREATE scripts for the backend

the issue im having is with design, as im not too experienced with the sql backend im just wondering how this would work in regards to multiple customers

ie, the database was designed so that the back end would sit on each individual company's server, then each user would have a copy of the front end to use
However with the new version we're giving custoemrs the option of either storing their backend on their own server or paying us an additional monthly fee to host and maintain their db on our server

If they go for the latter option im wondering, do i need to create a new copy of the database for each company? if so the connection string would need to be altered but im not sure how id go about altering the string for each company that uses the service
the other option is to create a new table for each company and then link it to pretty much every table in the db (and turn every Primary Key into a composite key, using the companies ID and whatever ID number is generated on the table in question)

Option A looks the simplest but i dunno how to implement it (im assuming ill need a Companies table which links to the Users table and a connection string would be retrieved by grabbing it from the Companies table? just seems very fiddle-y)
Option B can be accomplished easy enough, however the problem would be the generation of auto numbers
ie. Say ive got two companies, their ID's are 001 and 002, company 002 generates a few purchase orders, ID's 001-007, then company 001 generates an order, instead of their first PO being 001, its 008, then company 002 generates PO 009 and it goes from there, i can imagine this getting very confusing and dunno how to change the db structure so that it keeps track of numbers for each individual client, once again seems like it would be more trouble than its worth

can anyone offer any advice on what the industry standard is for this type of system?

 

[Ranman256]

I worked w a client that used 110 separate dbs (sql svr). 1 db for each co.
They all started the same, but each co wanted specific changes for their needs.
Sometimes this 'need' could be ported over to another co and became the upgrade for the main startup db template.

a crash would only affect 1 co, and not loose all.
If needed, an access FE could join tables from all db's to run stats on all 110 dbs.

 

[SQL_Hell]

Yes definitely multiple databases, one for each customer is the way to go.
If you try to use the same database for all customers you are gong to come unstuck pretty soon for the following reasons:

1. Most companies will not want to share a database with another customer, for data protection reasons.

2. If you have everything in the same database then how can you ensure they can't see each others data, you can't really. All it would take is a development mistake and one company receives the wrong data then you could have yourself a data protection nightmare.

3. If all data is in the same database and there is an outage, such as heavy locking or corruption, all customers will go down instead of just one.

4. If one customer suddenly decides then want an output of all their data, then it's easy with one database per customer, just send them a backup. Otherwise you could be having to write complex queries, but I am assuming your schema is fairly small for now.

5. Also with one database per customer, you can have different maintenance times, so one company might want their backups to run at 12:00am and another at 07:00am.


At many places where I have worked, the customer has their own databases and their own version of the website or application so they can have difference colour schemes and branding for their website, but it all comes from the same product code base. This also means you can satisfy new customer requirements, so if one company want additional functionality that other don't then you can do that change to their version of the website and database only.