3

I am working on a web application that will require users to have their own set of private data. My original plan was to create a stores table, a users table, and a user_stores intersecting table. Then I would, in the stores table, save the database name for that store (and create each store-specific database with an application user and password so the web application could always login).

Each store would have similar data (users, products, shipping methods, etc), and I know I can use foreign key references to tie everything together in one giant database. However, being that the data is very specific and potentially proprietary, would it be better to use my original design, or make a single database with everyone's data in there?

I am thinking for scaling concerns, separate databases would be better because we could put the more active accounts on their own (or more powerful) database servers and simply add a server location field in the stores table if we needed to. Additionally, it may be more secure because we could make add the user login information to the database and only give them access to their data (preventing one user from editing another user's stuff). My question is, are there concerns that I am missing though? Just about every post I have read about this says not to use the method I am thinking of, and I am no DBA. Any input would be helpful.

Additional Information: This will be hosted on a Dedicated Server that I will have root access to. I can create as many MySQL databases as I need to.

Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185
Mike
  • 1,718
  • 3
  • 30
  • 58
  • *One* database. As many tables as you need. 1 user == 1 row in users table, 1 product == 1 row in products table, etc. It sounds like you'll probably be coming up to speed on all three of 1) SQL, 2) HTML and 3) PHP or Java. You'll find good tutorials [here](http://www.w3schools.com/php/) – paulsm4 Apr 22 '13 at 16:45

2 Answers2

7

I would use a single database for sure. Use the following to get started. There are several reasons to go with a single db, however the biggest reason of all is to save you from a maintenance nightmare. If you have to change the schema, you will have a mess on your hands.

http://msdn.microsoft.com/en-us/library/aa479086.aspx

Scott Adams
  • 410
  • 3
  • 11
  • Only in a very extreme case would I ever go the route of having different databases for different users. Maybe for example if we are talking about a finite set of customers and you needed to have high customization from one customer to the next (Even then, I would probably not). I am talking different schemas, procs, and views based on users. However, it will not scale well at all as customers increased. You would have to make the same change to each database as you made changes. Not every dry code. It would be a total mess after a while. You do not want that trust me. – Scott Adams Apr 22 '13 at 18:45
  • A key point in the article is, "The shared-schema approach is appropriate when it is important that the application be capable of serving a large number of tenants with a small number of servers". I worked with company that had split out customers by DB. They only had 12 customers and it was still a nightmare. Don't do this to your future self. – Scott Adams Apr 22 '13 at 18:50
  • Since MySQL doesn't support schemas (that I am aware of anyway), I am guessing you recommend PostGres for this? – Mike Apr 22 '13 at 19:12
  • By schema, I am just referring to your database design such as tables, cols, ect... MySQL is a fine choice. – Scott Adams Apr 22 '13 at 19:14
3

In a multi-tenant database, database designers think about querying, cost, data isolation and protection, maintenance, and disaster recovery.

Multi-tenant solutions range from one database per tenant ("shared nothing") to one row per tenant ("shared everything"). This SO answer summarizes the tradeoffs. If you're designing a database that falls under some kind of regulatory environment (HIPAA, FERPA, etc.), that regulatory environment might trump all other considerations.

One database per tenant is a defensible decision in some cases. It's not clear whether that's the best answer in your case, though.

Community
  • 1
  • 1
Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185