Relational Database Question

[Shane]

Hello,

I'm pretty new to setting up databases, but so far I'm getting along
swimmingly. But I have one question. I'm setting up a database for a
client who wants to sell tickets to their theater online. There are a
LOT of different parameters and I'd like to set it up so that when a
client selects a play and date the price is automatically generated. I
have already figured out how to get what I want, but I believe there
is a better way, I'm just ignorant to it right now. Here is the deal:

5 plays a year
5 different show times (i.e. sunday matinee, weekday evenings,
friday/sat evenings, etc.)
Each play has a different price for each show time

So I have a table (ticket_price) now that looks like this:

play ticket_type price
----------------------------------
play_1 matinee 20
play_1 week_eve 25
play_1 fri_sat_eve 28
play_2 matinee 19
play_2 week_eve 23
play_2 fri_sat_eve 25

etc....

and the order (ticket_order) table:

order_id play ticket_type meal quantity
-----------------------------------------------------------
01 play_1 matinee yes 2
02 play_2 fri_sat_eve no 5

etc...

So I match the ticket with its price for by:

mysql-> select * from ticket_order, ticket_price
where ticket_order.ticket_type='week_eve'
and ticket_price.ticket_type='week_eve'
and ticket_order.play='play_1'
and ticket_price.play='play_1';

and from there I can use PHP to create a shopping cart type of thing.
But the ticket price table seems redundant to me. But for the life of
me I cannot think of how to fix it up. Or, perhaps, the above SELECT
is more complicated than it needs to be. I've poured over my books and
did searches, but nothing seems to touch on this particular subject. I
have a horrible feeling i'm missing something elementary!

Thanks!
Shane

[Bill Karwin]

Shane wrote:

5 plays a year
5 different show times (i.e. sunday matinee, weekday evenings,
friday/sat evenings, etc.)
Each play has a different price for each show time But the ticket price table seems redundant to me. But for the life of
me I cannot think of how to fix it up. Or, perhaps, the above SELECT
is more complicated than it needs to be.

The ticket_price table is not redundant, if the ticket price varies per
play and per show. It's okay to generate a few rows -- it sounds like
you're going to have only 25 new rows per year. This isn't an
excessively costly proposition.

You're making it a bit complicated in part because you have two columns
that identify a play, where you could have one. There's no exact right
way to do it, but here's how I would propose to design it:

create table ticket_price (
ticket_id integer not null auto_increment primary key,
play_title varchar(100) not null,
show_datetime datetime not null,
ticket_price numeric(5,2),
offers_meal tinyint not null default 0,
on_sale tinyint not null default 1
);

Create a separate record for ticket_price for each showing. Not only
for each of the five showings during the week, but for every showing for
that play for the entire season. This way, you can print the date &
time on each customer's ticket, you can track when a given showing has
sold out so you can stop selling tickets, and you can make reports at
the end of the season to find out which dates & times were most popular,
which might help plan for next season.

I've also added a proposed column "offers_meal" because some shows like
matinees may not have an option to buy a meal.

The ticket price might be redundant in typical cases, because it's often
going to be one price for a matinee vs. evening, or one day of week vs.
another day of week. But tracking it in this table enables you to vary
it based on date (maybe later in the run of the play, you would want to
reduce prices to keep filling the seats), and also avoids creating yet
another table, assuming you go with the one-record-per-datetime design I
describe above.

Now the ticker_order table:

create table ticket_order (
order_id integer not null primary key,
ticket_id integer not null references ticket_price,
quantity_seats integer not null default 1,
quantity_meals integer not null default 0
);

Now you get the ticket price for a given order with this simple query:

select p.ticket_price * o.quantity_seats
+ (MEAL_PRICE * o.quantity_meals) as order_total
from ticket_price p inner join ticket_order o
on p.ticket_id = o.ticket_id;

Just my suggestions. There are doubtless many ways to design this
solution. The complexity of tables and "redundancy" of data depends on
how many figures can vary independently of one another. There's also a
tradeoff between redundancy and complexity of the data model; sometimes
it's simpler to create a redundant data design (also known as a
non-normalized data model) than to break it down perfectly into tables
and sub-tables, and deal with the programming that would be required by
this.

Regards,
Bill K.

[Shane]

Thank you Bill for your enlightening description. That helps a lot.
Now, though, I'm wondering about populating the tables. I'd like to
set it up so that when we add plays, we can give the run time (ie.
from Feb. 2, 2004 to April 10, 2004) and have the database
automatically filled with a PHP script that will figure out the days,
prices and such. I wonder if there is anything else to take into
consideration with the database when anticipating this feature.

Thank you for your help.

Shane

[Bill Karwin]

Shane wrote:

Thank you Bill for your enlightening description. That helps a lot.
Now, though, I'm wondering about populating the tables. I'd like to
set it up so that when we add plays, we can give the run time (ie.
from Feb. 2, 2004 to April 10, 2004) and have the database
automatically filled with a PHP script that will figure out the days,
prices and such. I wonder if there is anything else to take into
consideration with the database when anticipating this feature.

Hmm. If the times are pretty uniform, for instance if matinees are
always at 2pm and do not include meals, you could create another table
that has some of the common show times, with any other relevant
attributes like that. For example:

create table show_times (
show_time_id integer not null primary key,
day_of_week enum(0,1,2,3,4,5,6) not null, # Sunday = 0
time_of_day time not null,
ticket_price numeric(5,2),
offers_meal tinyint not null default 0
);

Then use this to create a pick-list in your user interface, so the
person entering data for a new play can select which days & times the
play will be shown. Then based on the show times selected, your code
can generate the full list of showings between the play opening and closing.

Basically this is just a way to move some hard-coded values out of the
PHP code and into the database. That's often a good thing to do,
because it makes it easier to add more choices later.

Regards,
Bill K.

[Andreas Falck]

Bill Karwin <bi**@karwin.com> wrote in message news:<cb*********@enews3.newsguy.com>...

create table show_times (
show_time_id integer not null primary key,
day_of_week enum(0,1,2,3,4,5,6) not null, # Sunday = 0
time_of_day time not null,
ticket_price numeric(5,2),
offers_meal tinyint not null default 0
);
ERROR 1064: You have an error in your SQL syntax.

http://dev.mysql.com/doc/mysql/en/ENUM.html
"An ENUM is a string object.."

Ok, we try this instead (column names shortened to avoid line wrap):

mysql> create table show_times (
-> show_time_id integer not null primary key,
-> day_wk enum('0','1','2','3','4','5','6') not null,
-> tm_of_dy time not null,
-> tck_prc numeric(5,2),
-> off_ml tinyint not null default 0
-> );
Query OK, 0 rows affected (0.00 sec)

mysql> insert into show_times values (3.14, -1, 'never enough time..',
'way too expensive!!', 128);
Query OK, 1 row affected (0.00 sec)

mysql> select * from show_times where day_wk=0;
+--------------+--------+----------+---------+--------+
| show_time_id | day_wk | tm_of_dy | tck_prc | off_ml |
+--------------+--------+----------+---------+--------+
| 3 | | 00:00:00 | 0.00 | 127 |
+--------------+--------+----------+---------+--------+
1 row in set (0.00 sec)

mysql> select version();
+------------+
| version() |
+------------+
| 4.0.14-Max |
+------------+
1 row in set (0.00 sec)

Note:

* Select on an enum column with a numeric value returns rows matching
the corresponding index, not data.
* 3.14 is accepted as an integer and rounded to 3.
* -1 is not the set of valid enums. The default empty string is
inserted instead .
* Illegal time values are converted to '00:00:00'.
* Illegal numeric values are converted to zero.
* Out-of-bounds numeric values are clipped down to the appropriate
endpoint of the range.

MySQL behaves as documented, but probably not as expected.

Lesson learnt: *NEVER* trust MySQL to enforce data integrity unless
you are absolutely sure it performs as you expect.