Table design for ticket sales

I'm designing a database for a ticket sales system in PostgreSQL, where the admin user can create a batch of tickets for a specific event. All tickets for a given event have the same attributes (like eventId, name, price etc.) except for a serial number, which must be unique for each ticket.

What are the best practices for this situation? Should I treat each ticket as a record in a Ticket table, or should I have a TicketBatch table where I store the ticket information along with the amount available and implement the serial number logic somewhere else?

My concern is that each event can potentially have thousands of tickets for sale, so the one-ticket-per-row solution may not scale well and have slow queries over time. On the other hand, if I go the TicketBatch route, I may have problems keeping track of which tickets (identified by their serial number) were sold.

I can see no strong reason to avoid having a Ticket table that stores each serial number on a separate row, assuming that makes most sense for the application that will use this database. You should still normalise the overall design to avoid repeated data in this table.

"Thousands of rows" is pretty tiny for a relational database: you should easily be able to store all the tickets for thousands of events (~millions of rows) without any performance problems, provided you have clustered and indexed the table appropriately. This of course depends on your server hardware, and on how many events you expect to be servicing.

I assume events are short lived, and the record of tickets sold would not usually be needed after the event has happened. If so, you could easily have a TicketHistory table, and archive off rows to there once the event is over so the main table remains small and performant.

StackExchange Database Administrators Q#138247, answer score: 2