DB design: one large DB for all customers or many small DBs

Question

Looking for any suggestions or advise or even best practices. I have developed an online database using php and mysql. It allows companies to log complaints and resolutions etc. There is a user database for login and a cip database for logging the main data. 2 companies are trialing and testing the database. At the moment each company is using separate databases and separate html pages. I am wondering what the best way to add more companies.
Ideas that I have are:

1. To have one large database for users and one large database for cip and use a company id or similar to identify individual companies records in the database.

2. To use the same html pages for every company but select which database to use from the login details. So that each company will have a separate cip database but all companies use the same users database.

3. Or just keep everything separate for every company. (this might be really bad for doing updates)

I hope I have made myself clear and look forward to any suggestions.

Thanks

Answer 1

If you have 1 table called companies then each user can have a company_id so it relates to that company. You only need 1 database just use a one-to-many relationship.

Answer 2

Number 1 is best way to do so. Have one database for all the information you need and use IDs to identify companies. If you don't want to modify your current application files you may use this DB-layout, but create views, which simulate your old database structure.

Answer 3

1. To have one large database for users and one large database for cip and use a company id or similar to identify individual companies records in the database.

That's the best option. You can just store all the information in one database and differentiate them according to user login (where you can know which company the user is and store company id in session). However, careful with your codings as a mistake can show a company some information other company own.

Answer 4

Personally I would go for candidate #3. Except for the users part, these kinds of databases can grow large rapidly. To keep performance to a maximum it is advisable to keep your tables as small as you can. Selecting a different database based on the user's credentials shouldn't be hard to do, so application logic shouldn't suffer.

I understand your concern about maintainability. Does every customer live on the exact same app stack or do they have separate installations? Eitherway, maintenance should be easily scriptable to perform multi database updates. That shouldn't hold you back for choosing a design like this.

Answer 5

just a single database for users and companies will do it. and I think you`ll need some php, asp or jsp in your pages to identify users and query the database ...

Answer 6

I would go for solution (1) or in case you think you will have thousands of customers using your web application for (2).

The (3) would be definitely a nightmare to be mantained.

BTW , i suppose in solution(1) you think to use same html pages too like in solution (2), Whether you use on large DB or many small ones, I would strongly advise to use anyway the same application logic (html/php pages) always for all.

Answer 7

hello punk,

for first point

• To have one large database for users and one large database for cip and use a company id or similar to identify individual companies records in the database.

my opinion is okay. because databases are built and tune to store and manage large data's. but only think is you should design the table properly. avoid situation of data duplication and so. build and manage big dataset is good. but should focus on design. you should tune you tables. for that you can search google for database designing and perfomance tuning.

• To use the same html pages for every company but select which database to use from the login details. So that each company will have a separate cip database but all companies use the same users database.

the best is to build a basic template for all companies. this will help in managing you application. on login you select appropriate details what the company need or have and load it to the template. create a login table for companies, from their using an id point toto your information dataset and load it to a template. this will help you in debugging and its, its a standard way of making apps.

like facebook. every one have common home page template on login they filled with their relative data like pics, videos, post etc...

try to develop an app like that.

these are my suggestions..

Answer 8

This is an interesting case of a conflict between your convenience and your responsibility to keep your customers' data secure. For your convenience, it's pretty clear a multi-tenant application, as described in your option (1), will be the easiest to get working.

But you are buying yourself a significant problem for the future, should your business succeed. The way you have described your application, there is never a legitimate need for one of your customer companies to see data belonging to another. In fact, if they were to see one anothers' data, you would have a serious security breach to deal with. If the breach involved personal records, you would face business-threatening costs and penalties.

So if you do go for option (1) don't shortcut your code inspection and security testing. If you do poor security testing you'll be sorry, and worse, so will your customers.

For better security you may want to consider a combination of option (2) and (3): a single web app code base, moving your multiple-tenancy locus from the web app to the mySQL server. The mySQL security system is decently good at multiple tenancy: Wordpress, Drupal, and Joomla all work that way, and they're all widely deployed.

This approach has another advantage. If you do the development work necessary to build simple installation scripts for your web app and data base, you'll be able to provide your app to customers to run on their own servers if that's what they want.

I'm not saying avoid (1) completely. Your business might offer a hosted multi-tenant application (option 1) solution, with terms of service to cover your risks. And, for bigger or better-equipped customers you could offer the private solution.

Answer 9

One question I would want answered is do you ever need to see data across cutomers for your own reporting or use? In this case, you need to go with number one or you will have a nightmare to get good reporting.

Will you be doing any customization by customer? This would indicate that separating things out might be a better choice. If you will never customize, then don't separate out.

I have worked with systems in all of these options and the first one is by far the best for long term maintenance. However all are workable if you are organizaed and plan well. If you go for the separate otion, you must be able to push changes to all clients and thus must be doing changes to the database through scripts that are kept in source control. You may even need to keep a source control by database version, so that clients can choose to upgrade or not. In option 1 of course, no one has the option to stay on the old version. If that fits your business needs better, that is a plus for option 1.

I strongly agree with Ollie Jones, if you use option one, you must have a good database security design to prevent clients from seeing the data of other clients. We once moved a client from a server where they were the only client to a shared database and just one proc that missed asking for the client_ID (It wasn't needed in the old system and the developers had gotten sloppy) ended up emailing all the sales reps of all the other clients with information about the first client. This cost the company a lot of money (both to fix the problem, to send out email apologies and we almost lost a client as a result and had to give them some cost breaks to keep them) and many grovelling apologies and the developer just narrowly missed losing his job. Let this be a lesson you don't learn the hard way.

Answer 10

You'll find it a lot easier just to use a single database. You're on the right track, thinking about maintenance cost, etc.

Use properly designed/related tables -- for example:

Company Information Table
--------
Company_ID (primary key)
Company_Name
Other Fields...


Issue Table
--------
Issue_ID (primary key)
Company_ID (foreign key tied to the Company Information Table)
Issue_Text
Other Fields...

Hope that helps