Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

1 Answer 1

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.

share|improve this answer
    
Thanks for your help. I can't thumb up yet lol. –  Yoker Jun 12 at 21:55
    
@Yoker but you can accept the answer without any rep. –  Ayesh K Jun 13 at 0:43

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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