MySql table design : Use lots of rows or store the information formatted in one text field per row?

Question

I am trying to discover the best way to design my database to organize information related to events.

I have an events table which contains all the information about the event such as, a unique id, title of the event, venue etc.

Now each event can have multiple ticket types and the number and type of tickets will change with each event.

Is it better to have a events_tickets table which has a seperate row for each ticket type e.g.

event_id    ticket_type    price
1           standard       20
1           deluxe         40
1           cheap          10

Or is it better to have the table formatted so that the information is on one row?

event_id    ticket_information
1           standard:20,deluxe:40,cheap:10

If I use the first way I could end up with 10 rows per event which when multiplied by lots of events could become very large, whereas the second version could have problems with data integrity.

Answer 1

the first one... definitely. :) having as much of your data as separate as possible is ALWAYS the best way... it makes it much more usable and much easier to change/upgrade/expand the code later.

In fact I would have 3 tables: events, event_options and ticket_types

event_options would just be literally a link table between the events and the ticket_types, and can include other information you need to hold per event. This way it will make it easier still to a) search by ticket type and b) add more ticket types because when you come to add a new ticket type to an existing event (or something similar) you will have a lot more issues the second way.

Answer 2

The official answer is to do it the first way. If you only ever have exactly the same three types of tickets, then you can get on with having three "ticket price" fields. But otherwise, relational-purism tells you to go with the first.

I'm assuming that in any event you have an "events" table. Tell you what: search for "third normal form" on your favorite search engine, and you'll learn a lot about designing databases.

Answer 3

The first way is better. It is more normalised. Why does this matter? It means it's much easier to query your data. You don't want to use the second way, because it'll be really complicated and time-consuming to retrieve data at a later time.