Ticket-like table and growing database

Question

I manage a MySQL database through phpMyAdmin. I have a table for cinema cupouns tickets. I upload a bunch of tickets, and then I assign tickets to users.

Each ticket contains,

id          -> int(11) unsigned PRI auto_increment // should it be bigger?
ticket_id   -> varchar(20) // should this be the primary key?
type        -> enum('3d', 'common', 'monster')
price       -> int(11)
user        -> varchar(16)
expire      -> date 
updated     -> on update CURRENT_TIMESTAMP
created     -> timestamp

So, when tickets are created, user fields are empty, that's how I check how many are available. When a user needs tickets I select the top ones (with empty user value) limiting by the quantity and assign them to the user in the user field.

Sample data

ID  ticket_id               type    price   user        expire          updated                 created
1   V100070263526SA5K3W     3d      45      27060192    2015-07-30      2015-04-01 12:10:06     2015-03-31 08:39:19

I wonder if I'm doing fine, or how should I optimize the design in terms of a database table, considering a bunch of 400 new ticket uploads happen every month.

Answer 1

In general, this design will do the job without problems, specially that you only handle 400 rows per month, which is relatively small number.

However, I have few recommendations to improve the design of the table:

1. price is better to be decimal, unless you want to store the price in "cents". i.e. if the ticket price is $5.99. you either have to store it as 599 and let the application process it when doing calculation, or viewing it; or, you store it in decimal(5,2)
2. user field is better to be an integer, and the user ID in the users table is better to be an integer
3. Your primary key is good. Don't change it to ticket_id.
4. Maybe a used flag field would be more clear to know which tickets are already used (This is personal preference)
5. Depending on the queries you run against this table you may create the required indexes.