When should I use a new table?

If a database is to hold a large amount of eg tickets for concerts, should I make a new table for each concert, then keep a table connecting venues with concerts?

I would like each ticket ID for each concert to start from 1, not be a running total of every single ticket.

Or should I keep them in one table and have 2 seperate columns for (ticket ID) AND (ticket number for that event)?

Your second option is a better solution. Add a new column to your tickets table, called ticket_concert_number. The logic to begin at 1 for each new concert is something that's probably best controlled in your application. You should also have a column called concert_id so you know which concert the ticket is for.

If you create a new table for each concert, you'll quickly fill your database with many many tables with relatively few records. This could lead to performance problems (when compared to a single table that's properly indexed). And if the table structure changes, you need to decide if you're going to propagate the structural changes to all of the existing tables, and if you don't, you'll need to be aware of the fact that you will have to query all the old tickets tables differently depending on what "version" of the original structure they were based on. And then you also have to consider how your application will even find the correct table, when the tables are being generated for every new concert.

StackExchange Database Administrators Q#68177, answer score: 9