2

I want to make an event (concert, theater, cinema,..) booking website

venues table:

-ID
-name
-address

stages table:

-ID
-venue_id
-title
-seats

-seats is json and contain seat row and column

  1. First question is which one is better? Create another table for the seats of each Stage or a column for any field of seats properties?

Then I need to have a showtimes table to set the price and event_id for each seat.

  1. I want to know, if there is an optimal way to create the showtime table instead of creating a row for each seat?
  • 2
    You have committed the mistake number #1 when designing a schema- thinking there is a good or a bad static design- when there are no good or bad answers, except when taking into account what the final queries you are going to do look like, then optimize your design for that (eg. optimizing for reads or writes? how many of each? what selects will be the most common?, etc.). – jynus May 18 '17 at 13:31
2

When designing a new database, it's important to know before hand how it will be used. Of course, it's difficult to have perfect knowledge of exactly how your database will be used before hand, because you may be prototyping, your business needs may change over time, etc.

I've found that a good rule of thumb for designing a relational database for a transaction processing system is to assume that each tangible thing your system tracks gets its own table.

Consider the following ERD for your situation:

ERD

In your case you have VENUEs that each have one or more STAGEs. Each stage has many SEATs. However, note that you want to keep track of PRICEs for seats, so for the sake of sanity in maintaining pricing data, your seats are arranged into SECTIONs. The price is set at the section level instead of at the individual seat level. The price also depends on the show time (EVENT) because you might have different prices for different days of the week or different times of day (week day matinee is cheaper than Saturday evening, for example).

You have multiple show times, but there is probably going to be many things common to an event with multiple show times, so you want an EVENT table and a child SHOW table. This lets you keep show and event (show time) information in third normal form (3NF) which is always a good place to start with a transactional system database.

Assuming that you also want to keep track of who is actually in each seat, you need to have TICKETs and CUSTOMERs in your database as well.

To address your specific question about how to track seats, as rows or as columns, I strongly suggest you track seats as rows. Relational database management systems like MySQL are built to easily handle rows of data. If you handle seats as columns, you would probably need a different seat table for each stage, since the seats will be different for each one. That would make your queries very awkward and complex. If you are going to use a relational database for your data, then use it the relational way and put things in rows and facts about things in columns. Your code will be simpler and your life will be better.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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