1

I´m new on php/mysql, and i´m codding a simple CMS. But in this case i will host multiple companies (each company with their multiple users), that pays a fee to use the system.

So... My question is about how to organize the Data Base... Talking about security, management and performance, i just want to know the opinion of ou guys of wich of these cases is the best:

  1. Host all companies on a single DB and they get a company id to match with the users.

  2. Each company have a separated DB that holds the users in there (and dont need the companies id anymore).

I would start the development following the first situation... But than i thought if i have some hacker attack / sql injection, every client would be harmed. Having separated DBs, the damage will get only one client. So maybe the 2nd situation could be better in terms of security. But could not say the same about management and performance.

So, based on your experience, any help or tip would be great!

Thanks in advance, and sorry about my poor english.

Improve this question
1
  • 1
    I would lean towards separate databases. 1) You would have the option to provide the companies direct database access, if required. 2) Customising for each customer becomes easier, as you are no longer using (as many) composite primary keys. 3) The reserved data of each company is in a separate place, and there is much less risk that a company sees another's data. Commented Apr 24, 2013 at 14:24

4 Answers 4

Reset to default
2

I would go for seperate DBs. But not only for hacking.

Scalability: Lets say you have a server that handles 10 websites, but 1 of those websites in growing fast in requests, content, etc. Your server is having a hard time to host all of them.

With seperate DB's it is a piece of cake to spread over multiple servers. With a single one you would have to upgrade you current DB or cluster it, but that is sometimes not possible with the hosting company or very expensive.

Performance: You they are all on 1 DB and data of multiple users is in 1 table, locks might slow down other users.

Large tables, mean large indices, large lookups, etc. So splitting to diffrent DB's would actualy speed that up.

You would have to deal with extra memory and CPU overhead per DB but they normaly do not have an amazingly large impact.

And yes, management for multiple DBs is more work, but having proper update scripts and keeping a good eye on the versions of the DB schema will reduce your management concerns a lot.

Update: also see this article. http://msdn.microsoft.com/en-us/library/aa479086.aspx

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Hey, thank you so much for the tip... So i will go on multiple DBs as you suggest! And the article is on my favorite. Going to read it this night!
0

Separate DBs has many advantages including performance, security, scalability, mobility, etc. There is more risk less reward trying to pack everything into 1 database especially when you are talking about separate companies data.

Improve this answer

Comments

0

You haven't provided any details, but generally speaking, I would opt for separate databases.

Using an autonomous database for every client allows a finer degree of control, as it would be possible to manage/backup/trash/etc. them individually, without affecting the others. It would also require less grooming, as data is easier to be distinguished, and one database cannot break the others.

Not to mention it would make the development process easier -- note that separate databases mean that you don't have to always verify the "owner" of the rows.

Improve this answer

Comments

0

If you plan to have this database hosted in a cloud environment such as Azure databases where resources are (relatively) cheap, clients are running the same code base, the database schema is the same (obviously), and there is the possibility of sharing some data between the companies then a multi-tenant database may be the way to go. For anything else you, you will probably be creating a lot of extra work going with a multi-tenant database.

Keep in mind that if you go the separate databases route, trying to migrate to a multi-tenant cloud solution later on is a HUGE task. I only mention this because all I've been hearing for the past few years around the IT water coolers is "Cloud! Cloud! Cloud!".

Improve this answer

2 Comments

The plan is really to go on cloud hosting... But at first i will go with resseler hosting plans until i got the minimum incoming to pay the cloud plans.
You can still host multiple databases in the cloud and not go for a multi-tenant database. Having reread your question, my main concern for you would be that you say you are new to php/mysql, and as a beginner i would not recommend adding the complexity of a multi-tenant database; nothing is ever "simple".

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.