Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2009
    Posts
    3

    Web developer asking for review of data model

    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
    Attached Thumbnails Attached Thumbnails datamodel_simplified_v01.gif  

  2. #2
    Join Date
    Sep 2009
    Posts
    3
    As I've begun to play with actually creating these tables and filling them with some test data, I've noticed two things:

    1. Having both Tickets and TicketCategories seems redundant - I'm pretty sure I could combine them.
    2. 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.

  3. #3
    Join Date
    Sep 2009
    Posts
    3

    Question Hoping to get some feedback before I move on...

    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
    Attached Thumbnails Attached Thumbnails datamodel_simplified_v02.gif  

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •