4

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 asynchronous 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?

Community
  • 1
  • 1
Julian Popov
  • 17,401
  • 12
  • 55
  • 81
  • 5
    You've heard, "Relational Databases Don't Scale." I've heard pigs fly. Think of the most complicated, biggest, data processing in the world and it's a relational database. Only recently have NoSQL databases taken VERY specific problems and fixed them by altering the relational paradigm. – Stephanie Page Mar 01 '11 at 20:47
  • Actually when you think about some of the biggest, most complicated data processing, you'll probably into stuff like CICS, which is like my dad's NoSQL... ;-) – mhanisch Mar 02 '11 at 14:29
  • 1
    Badly-designed "relational" databases with ID keys do not scale, well-designed Relational databases scale beautifully. So get the design right. And ensure that you understand transaction control, OLTP and concurrency requirements, and implement them. All high-end Relational database platforms provide asynchronous operation, you do not have to do it yourself. The low-end freeware platforms do not even have a true server architecture, so watch out. – PerformanceDBA Mar 07 '11 at 10:57
  • 1
    But are they web scale? http://www.youtube.com/watch?v=b2F-DItXtZs – Adam Robinson Mar 07 '11 at 13:21
  • @Adam. It is a sad day when full grown adults on a technical website have to listen to cartoon characters selling new technology. I post technical facts, based on computer science and experience; not opinions; and not based on untested technology. Oh and MySQL is not IEC/IOS/ANSI standard SQL, nor is it enterprise class, so yes, MyNonSQL does not scale, but that does reflect on SQl or enterprise class SQL platforms. Finally most Relational databases are poorly designed, and therefore not scaleable; again that does not reflect on well-designed Relational databases, that scale beautifully. – PerformanceDBA Mar 07 '11 at 23:17
  • 2
    @PerformanceDBA: It's a joke, and one that you evidently don't get, as the video is written *against* the NoSQL fanatics that is caricatures. I would be delighted if you would simply pretend that I did not exist, as I find your comments exhausting. – Adam Robinson Mar 08 '11 at 02:41

4 Answers4

5

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.

Frank Heikens
  • 117,544
  • 24
  • 142
  • 135
  • Assume you have an Event table which has the number of seats in one column and a Order table which has (order_id, event_id, seats). Now every new entry to Orders needs to compare the events seats with the sum of already sold seats. To avoid seelling to many, that needs to be in one transaction. And you need Serializable. That drops performance quite a bit. – Martin Thoma Jul 27 '23 at 17:06
  • @MartinThoma That highly depends on your logic. As soon as someone selects an X number of tickets, you can update this number of tickets with a timestamp. skip locked does magic to performance because it does skip all locked records. You don't need a serializable transaction, you don't want it either. – Frank Heikens Jul 27 '23 at 17:37
  • So you would create the tickets upfront, even if they are not tied to specific seats? I don't understand the comment about "skip locked". And the reason why I think I need serializable is as I want to avoid selling more than I have (overallocation) which could happen with serialization anomalies (as I need the sum of all ticket counts compared to the `event.number_of_seats`) – Martin Thoma Jul 27 '23 at 19:47
  • 1
    @MartinThoma Yes I would create the tickets beforehand, because you know how many tickets you can handle. It's just an id and a timestamp for locking, you can handle billions of records/tickets. When you create the tickets, you already check `event.number_of_seats `. You don't check this (again) while selling the tickets, no need for. – Frank Heikens Jul 27 '23 at 20:50
  • But how would you allocate the tickets in a way that doesn't cause overlaps regularly? ... I mean, most people just buy one, but it follows a power law distribution: Some people/organizations buy a lot of tickets. Is there a smarter way than just doing it randomly + hoping for the best + having an automatic retry-mechanism? – Martin Thoma Jul 28 '23 at 06:31
  • 1
    @MartinThoma There is imho nothing wrong with a random ticket, it's just a ticket. The exact seat can be selected later on in the proces. But when you have half a million people trying to buy one of the 10000 available tickets, you first have to get rid of the (at least) 490000 people that won't get a ticket. A retry-mechanism and other steps to improve the experience are all helpful. – Frank Heikens Jul 28 '23 at 13:14
  • Thank you for the idea. I need to think about that / simulate it :-) – Martin Thoma Jul 28 '23 at 16:42
4

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

Dmitri
  • 34,780
  • 9
  • 39
  • 55
  • 3
    I wouldn't lock the entire table when I only have to lock a single record (a.k.a. ticket). Performance will be horrible. – Frank Heikens Mar 01 '11 at 14:38
  • 1
    True, but you said "And database will lock those tables for you". A table lock is something very different and something you don't want. And sometimes you have to lock a record yourself, SELECT ... FOR UPDATE is a common statement in applications like this. – Frank Heikens Mar 01 '11 at 14:51
  • Frank, I think it was just a semantic issue... but I plussed your comment cuz it's true – Stephanie Page Mar 01 '11 at 21:12
  • @Steph. It is **not** "semantic". Read up on ISO/IEC/ANSI standard SQL Isolation Levels and transaction control commands. – PerformanceDBA Mar 07 '11 at 10:51
  • I don't think I will. Thanks for the tip, though. – Stephanie Page Mar 08 '11 at 21:58
  • @Steph. That's fine. But in that case, you are in no position to make declarations re what is and is not "semantic" re the subject. – PerformanceDBA Mar 10 '11 at 04:22
3

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.

Community
  • 1
  • 1
Nathan DeWitt
  • 6,511
  • 8
  • 46
  • 66
2

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.

Randy
  • 16,480
  • 1
  • 37
  • 55