What database solution will you suggest for competitive online tickets sale

Can you please give me an database design suggestion?

I want to sell tickets for events but the problem is that the database can become bootleneck when many user what to buy simultaneously tickets for the same event.

• if I have an counter for tickets left for each event there will be more updates on this field (locking) but I will easy found how much tickets are left
• if I generate tickets for each event in advance it will be hard to know how much tickets are left

May be it will be better if each event can use separate database (if the requests for this event are expected to be high)?

May be reservation also have to a开发者_如何学Csynchronous operation?

Do I have to use relation database (MySQL, Postgres) or no relation database (MongoDB)?

I'm planing to use AWS EC2 servers so I can run more servers if I need them.

I heard that "relation databases don't scale" but I think that I need them because they have transactions and data consistency that I will need when working with definite number of tickets, Am I right or not?

Do you know some resources in internet for this kind of topics?

If you sell 100.000 tickets in 5 minutes, you need a database that can handle at least 333 transactions per second. Almost any RDBMS on recent hardware, can handle this amount of traffic.

Unless you have a not so optimal database schema and/of SQL, but that's another problem.

First things first: when it comes to selling stuff (ecommerce), you really do need a transactional support. This basically excludes any type of NoSQL solutions like MongoDB or Cassandra.

So you must use database that supports transactions. MySQL does, but not in every storage engine. Make sure to use InnoDB and not MyISAM.

Of cause many popular databases support transactions, so it's up to you which one to choose.

Why transactions? Because you need to complete a bunch of database updates and you must be sure that they all succeed as one atomic operation. For example: 1) make sure ticket is available. 2) Reduce the number of available tickets by one 3) process credit card, get approval 4) record purchase details into database

If any of the operations fail you must rollback the previous updates. For example if credit card is declined you should rollback the decreasing of available ticket.

And database will lock those tables for you, so there is no change that in between step 1 and 2 someone else tries to purchase a ticket but the count of available tickets has not yet been decreased. So without the table lock it would be possible for a situation where only 1 ticket is left available but it is sold to 2 people because second purchase started between step 1 and step 2 of first transaction.

It's essential that you understand this before you start programming ecommerce project

Check out this question regarding releasing inventory.

I don't think you'll run into the limits of a relational database system. You need one that handles transactions, however. As I recommended to the poster in the referenced question, you should be able to handle reserved tickets that affect inventory vs tickets on orders where the purchaser bails before the transaction is completed.

your question seems broader than database design.

first of all, relational database will scale perfectly well for this. You may need to consider a web services layer which will provide the actual ticket brokering to the end users. here you will be able to manage things in a cached manner independent of the actual database design. however, you need to think through the appropriate steps for data insertion, and update as well as select in order to optimize your performance.

first step would be to go ahead and construct a well normalized relational model to hold your information. second, build some web service interface to interact with the data model then put that into a user interface and stress test for many simultaneous transactions.

my bet will be you need to then rework your web services layer iteratively until you are happy - but your database (well normalized) will not be cusing you any bottleneck issues.