Web developer asking for review of data model

nselikoff · #1 (**permalink**) 09-04-09, 11:40

Hi all,

I've already found a lot of useful information in this forum, so I'd first like to say thanks to all of the folks who donate their time to answer questions and point people in the right direction!

I'm designing a web application at work that will allow guests to make reservations online for our dinner theater show. This will be the most complicated database driven web app that I've created, and I've been doing my best to research and learn more database design so I do this right.

So, I'd like to make sure I'm on the right track. I've simplified this data model quite a bit to start. Here it is (also see attached image):

Reservations
* Reservation Number (pk)
* Date Reservation Made

ReservationTickets (linking table)
* Reservation Number (fk, cpk)
* Ticket ID (fk, cpk)
* Showtime (fk, cpk)
* Quantity

Shows
* Showtime (pk)
* isSoldOut
* isCancelled

Tickets
* Ticket ID (pk)
* Ticket Category Code (fk)

TicketPrices
* Ticket ID (fk, cpk)
* Valid From Date (cpk)
* Price
* Currency Code (fk)

Taxes
* Ticket ID (fk, cpk)
* Valid From Date (cpk)
* Tax Rate

TicketCategories (validation table)
* Ticket Category Code (pk)
* Ticket Class
* Ticket Description

Currencies (validation table)
* ISO Currency Code (pk)

I'm thinking of the ReservationTickets table like a classic OrderDetails table, and the Tickets table like a classic Products table. I've included Showtime as a part of the composite key in ReservationTickets, because guests are able to buy tickets for multiple showtimes in one reservation (e.g. I could buy 2 child and 2 adult tickets for Showtime A, and 1 adult ticket for Showtime B, all in one reservation).

I've tried to design the TicketPrices and Taxes tables to easily support price changes over time and negate the need for calculated/derived values to exist in the Reservation. From what I've read about invoicing and sales receipts, you can either store the historical price data or denormalize the design and store the "applied" price of the product or service.

The reason the Taxes are associated with the Tickets and not the Reservation as a whole is because of the way the accounting is done at this company. I can get into more details on that if necessary.

Things I'll be adding to this design: discounts, guests (i.e. customers), employees, notes, i18n/translations, payments, and reservation status.

Any glaring (or subtle) errors in my data model so far?

Thanks,

Nathan

nselikoff · #2 (**permalink**) 09-04-09, 13:25

As I've begun to play with actually creating these tables and filling them with some test data, I've noticed two things:

Having both Tickets and TicketCategories seems redundant - I'm pretty sure I could combine them.
The Taxes table doesn't seem right. I have to enter a record in the Taxes table for each Ticket (infant, child, and adult). It is never the case that tickets will have different tax rates from each other, though the tax rate as a whole might change, and still needs to be calculated on individual tickets, versus on the total price of the reservation. Not sure what to do about this.

nselikoff · #3 (**permalink**) 09-08-09, 11:15

I've made a few changes based on my initial observations (see above) and this is what I have now (also see attached image):

Reservations
* Reservation Number (pk)
* Date Reservation Made
* Tax Code (fk)

ReservationTickets (linking table)
* Reservation Number (fk, cpk)
* Ticket ID (fk, cpk)
* Showtime (fk, cpk)
* Quantity

Shows
* Showtime (pk)
* isSoldOut
* isCancelled

Tickets
* Ticket ID (pk)
* Ticket Class
* Ticket Description

TicketPrices
* Ticket ID (fk, cpk)
* Valid From Date (cpk)
* Price
* Currency Code (fk)

TaxCodes
* Tax Code (pk)
* Description

SalesTaxRates
* Valid From Date (cpk)
* Tax Rate (cpk)
* Tax Code (fk)

Currencies (validation table)
* ISO Currency Code (pk)

Wondering how hard it would be to get the data I need from this model, I wrote a query to return Reservations with the total number of tickets, total cost, and total amount of tax paid:

Code:

SELECT ReservationNumber, 
sum(Quantity) AS NumTickets,
sum((Price + Tax) * Quantity) AS Total,
sum(Tax * Quantity) AS TaxPaid
FROM (
SELECT r.ReservationNumber,
rt.Quantity,
tp.Price,
round(tp.Price * (str.TaxRate/100.0), 2) AS Tax
FROM Reservations r
JOIN ReservationTickets rt ON r.ReservationNumber = rt.ReservationNumber
JOIN Tickets t ON rt.TicketID = t.id
JOIN TicketPrices tp ON t.id = tp.TicketID
JOIN TaxCodes tc ON r.TaxCode = tc.TaxCode
JOIN SalesTaxRates str ON tc.TaxCode = str.TaxCode
) t
GROUP BY ReservationNumber

Which gives me something like this:

Code:

ReservationNumber  NumTickets  Total   TaxPaid
1                  3           95.02   6.22
2                  4           255.96  16.76
3                  5           221.07  14.47

Any feedback on this data model?

Thanks,
Nathan

Internet Services

Web Development

Digital Marketing

Consumer Tech