3

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.

Improve this question
2
  • What ticket information would be common to all tickets in a batch? Would most of the information be the same? The use of a TicketBatch table makes sense if you actually have ticket batches (real or virtual), and the batches have a meaningful purpose and you want to record it.
    – Ezequiel Tolnay
    Commented May 12, 2016 at 0:37
  • Yes, @ziggy-crueltyfree-zeitgeister. Most of the ticket info would be the same for every ticket. Only their serial number must be unique. A ticket batch doesn't really have any meaningful info for my purposes, that I need to keep track of. My application only need to deal with the individual tickets. My concern was more about performance, disk usage, implementation complexity and general best practices applied to this situation.
    – mgaldieri
    Commented May 12, 2016 at 5:50

1 Answer 1

Reset to default
2

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.

Improve this answer
2
  • Thanks for your response. Storing each ticket as a separate record was my obvious choice, but recently I came across a database for traffic tickets in which the tickets were assigned to traffic agents in batches, and stored as such in the database. The application then was responsible for keeping track of which ticket was issued. Unfortunately I've never had the chance to ask the DBA why it was modeled that way.
    – mgaldieri
    Commented May 12, 2016 at 5:59
  • +1 for the TicketHistory table tip.
    – mgaldieri
    Commented May 12, 2016 at 6:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.