I'm presently planning for a new system in PHP/MySQL and wish to make certain my database are designed for the quantity of data that i'm likely to store. Among the options that come with my new project is really a "messages" feature like Facebook. I wish to make certain I create the perfect experience for that consumer. The web site will ultimately handle thousands of customers with potentially countless messages with each other. What will be the ultimate way for that database design? Is MySQL the right database to make use of?

Facebook began with MySQL plus they only gone to live in Cassandra once they had 7TB of mailbox data for more than 100 million customers.

Source: Lakshman, Malik: Cassandra - A Decentralized Structured Storage System.

MySQL doesn't have trouble with millions or 100s of countless records as lengthy while you design you database properly.

That being stated, a "messages feature like Facebook" is a nice broad definition. Generally, you'd define a messages table that links each message towards the user that produced it (ie, possess a userId column within the messages table). If you would like messages to visit multiple customers, you've got a message_recipients table determining the 1-to-many relationship by storing multiple records composed from the messageId along with a recipientId. Add the correct indexes to those tables and you are 80% of how there.

That being stated, that remaining 20% could be a killer. Regrettably, the way you make use of your database will figure out what else you must do, and you'd have use a much more detail regarding your application before individuals choice can be created. For instance, you should consider getting auto-archiving solution which will keep the primary table relatively small, and moves old data to backup tables that may be utilized if required. You most likely will not need this immediately, however it may help later on.

If you're planning to deal with considerable amounts of information (obviously millions does not even compare to being approved as large), then employ a datbase professional. Efficient and effective database design for big data sets is really a complex problem as well as a professional.

In response to your question yes mysql are designed for countless records easily when the design is nice and will also be a nightmare when the design isn't good, virtually like every other modern datbase.

As lengthy while you setup your tables to become relational and hang the associations between tables, MySQL ought to be fine.

Might I additionally suggest Postgres?

If you're on a tight budget, begin with MySQL and employ something like Zend::DB or on the greater level Doctrine.

It's more essential to really make it simple to switch DMBS then to select your DBMS at the start.

You're not so precise on what you would like to understand. Okay. I'll try to ensure you get advice.

  1. Normalization
  2. Indexes
  3. MyISAM for tables under high load
  4. Denormalization (sic!), however, you should understand what's happening
  5. Sharding
  6. Plain and simple DB layer for versatility

I needed by way of thanking everybody for the valuable input. I left the question a little broad deliberately. I'll continue using MySQL for the time being using the proper tables and associations. Many thanks!

Sharding is not essential for your "broadly" based needs...I've worked having a fair quantity of data and did not even consider partitioned tables and shard implementation until there have been numerous tables housing on the billion records (then joining individuals might get just a little slow). Index your tables with wise secrets, and you'll even think about using an eav type structure to help keep the tables narrow as well as reducing yourself of null returns on queries.

Above was written while half asleep so ignore typos )