how to set up multi-user web app database

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
kevlaw
Forum Newbie
Posts: 3
Joined: Tue Sep 21, 2010 2:15 pm

how to set up multi-user web app database

Post by kevlaw »

Probably, this is incredibly simple and forgive me if this is a stupid question..

I currently have a Microsoft Access app for a niche market- it runs as a client/server setup on each of my client's office networks. I have around 60 or so clients. I need, desperately, to create a 'cloud' or web version of this application and charge a monthly subscription for the use of it (the app- web or not- is exactly the same for all my clients, only their data is unique). I realize Access isn't really right for the job. From what I gather it would be PHP or Java along with an SQL or mySQL database.

The fundamental issue I can't seem to find an answer to- I'm not a web programmer, just Access- is.... If you have a web page where clients first enter a user name and password to access just their particular data, is the back end database just one gigantic database file or does each 'user' have their own unique database file, generated when they set their account up and pointed to by the log in web page? I need to know what the 'industry standard' is on this as it would help me begin to look at the right software.

For example:
It's possible that there could be 3000 client accounts (each with from 1 to 10 or so users using the same data set, so there might be multiple people accessing the same data at the same time, for just one client account), and each client account might have 10,000 or so records, so a single database file seems huge to me. So would there be 3000 separate data files, one for each client account? Again, the way they'd use the app is just go to a web page- www.myapplogon.com, enter they're user name and password, and then they'd be taken to the web app- www.mywebapp.com- that would have just their own particular data.

I know you could query even a giant database to just pull the user name's data, but I'm not clear on exactly how this would work. Again, I'm looking for the 'best practice' on this as I need to keep the data absolutely reliably separated between client accounts.

I'm trying to set up a monthly subscription, web-based version of the desktop software I already have. Any help PHPdn might be able to give would be greatly, greatly appreciated. Thank you very much for your time.
JakeJ
Forum Regular
Posts: 675
Joined: Thu Dec 10, 2009 6:27 pm

Re: how to set up multi-user web app database

Post by JakeJ »

Generally speaking, unless there is a very specific reason to do otherwise, you want to keep all the client data in the same database. The queries that pull the data will be based on the log in credentials of the client so there is no chance of one client getting the data of another client provided you take all the standard precautions.

If you're totally new to php/mysql, you might want to hire some help to at least get you started if you're pressed for time. But, if you have the time to figure it out yourself, then go for it.

You won't find the basics of Access and MySQL queries a whole lot different on a fundamental level, but the methods for getting it in to and out of the database won't be familiar to you at all.

Tutorials. They're all over the internet.

Then, when you have specific questions, post them back here.

Good luck!
kevlaw
Forum Newbie
Posts: 3
Joined: Tue Sep 21, 2010 2:15 pm

Re: how to set up multi-user web app database

Post by kevlaw »

Ah, that's what I needed to know. I guess then it would be ok if there were eventually close to a million records in the database? There would probably be around 60 tables or so with a handful potential having that many records. I just wanted to make sure that was the basic architecture and then, after that, I'll be able to move forward in a more detailed way. Thanks very much for your advice and insight!
JakeJ
Forum Regular
Posts: 675
Joined: Thu Dec 10, 2009 6:27 pm

Re: how to set up multi-user web app database

Post by JakeJ »

Sure, MySQL can handle that many records with no problem.
kevlaw
Forum Newbie
Posts: 3
Joined: Tue Sep 21, 2010 2:15 pm

Re: how to set up multi-user web app database

Post by kevlaw »

that's what I needed to know- not having any experience with that- just MS Access which cannot handle massive volume. Thanks for the advice!
Post Reply