Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2002

    Unanswered: database design for ticketing system; user ticket quotas

    My question takes two parts; firstly, is the new table that I'm proposing going to handle the business logic I describe below, and secondly, if I put the new table in, how in hell do I use it?

    Right; present schema attached.

    The idea, which I hope is fairly clear from the schema, is that you send it a buch of parameters about the event, admission date, etc, and it will return all tickets matching those parameters. An example stored proc for this is below:

    CREATE PROCEDURE [dbo].[getSingleTicketsByParameters]
    @eventId			INT,
    @standId			INT,
    @admissionDateId	INT,
    @bookingDateId		INT,
    @concessionId			INT,
    @bookingMinQuantityId		INT,
    @bookingMaxQuantityId		INT,
    @membershipId			INT
    FROM [tblTickets]
    LEFT JOIN [tblEvents] ON [tblEvents].[id] = [tblTickets].[event_id]
    LEFT JOIN [tblStands] ON [tblStands].[id] = [tblTickets].[stand_id]
    LEFT JOIN [tblBookingDates] ON [tblBookingDates].[id] = [tblTickets].[booking_date_id]
    LEFT JOIN [tblTicketConcessions] ON [tblTicketConcessions].[id] = [tblTickets].[ticket_concession_id]
    LEFT JOIN [tblBookingQuantities] AS tblBookingMinQuantities ON [tblBookingMinQuantities].[id] = [tblTickets].[booking_min_quantity_id]
    LEFT JOIN [tblBookingQuantities] AS tblBookingMaxQuantities ON [tblBookingMaxQuantities].[id] = [tblTickets].[booking_max_quantity_id]
    LEFT JOIN [tblAdmissionDates] ON [tblAdmissionDates].[id] = [tblTickets].[admission_date_id]
    LEFT JOIN [tblMemberships] ON [tblMemberships].[id] = [tblTickets].[membership_id]
    WHERE 1=1
    AND ([tblEvents].[id]=@eventId OR @eventId=0)
    AND ([tblStands].[id]=@standId OR @standId=0)
    AND ([tblTicketConcessions].[id]=@concessionId OR @concessionId=0)
    AND ([tblAdmissionDates].[id]=@admissionDateId OR @admissionDateId=0)
    AND ([tblBookingDates].[id]=@bookingDateId OR @bookingDateId=0)
    AND ([tblBookingMinQuantities].[id]=@bookingMinQuantityId OR @bookingMinQuantityId=0)
    AND ([tblBookingMaxQuantities].[id]=@bookingMaxQuantityId OR @bookingMaxQuantityId=0)
    AND ([tblMemberships].[id]=@membershipId OR @membershipId=0)

    So. It's all about to get horribly, horribly complex (well, it is to me) so take a deep breath.

    Tickets are subject to quotas. However, quotas are subject to... well, at the moment they can be based on event, stand, admission date, concession, or any combination of these. They can also be based on an individual ticket. All quotas, however, are annual; they only apply to ticket purchases in the same year. For example:
    - you can't buy more than 4 tickets for date A, in stand B at event C, per year.
    - you can't buy more than 2 tickets for stand D per year.
    - you can't buy more than 4 of ticket number 123.

    Now, I'm thinking that all I need to manage this is one table, and it's going to look a little like this:

    id INT PK
    ticket_id INT FK
    event_id INT FK
    stand_id INT FK
    admission_date_id INT FK
    quota INT

    So, if I put a record in there with an event, stand and admission date - and a quota - then I've met the first business rule that I described above. If I put in a record with just a stand id and a quota, then I've met the second sort. If I put in one with just a ticket id and a quota, then I've met the third.

    Now we return to that big SQL statement above. The one that says "get me all eligible tickets that match these parameters". And it's got to get a lot bigger because I now need to not only join in tblQuotas, to see if any quotas apply to the ticket I've chosen (or to the event, stand, etc that make it up), but I've also got to join in tblBasket, and tblOrders, and tblUsers, to find out how much of any particular quota they've already used up in previous orders. Although that's only orders placed in the current year, mind. And of course I also now haveto pass the users ID in as a parameter so I can look up their order history.

    Your head's hurting too, right?

    So... this is where I've got to:
    SELECT SUM(ticket_quantity) FROM [tblBasket]
    INNER JOIN [tblTickets] ON [tblBasket].[ticket_id] = [tblTickets].[id]
    INNER JOIN [tblOrders] ON [tblBasket].[order_id] = [tblOrders].[id]
    [tblTickets].[stand_id] = @standId
    AND [tblOrders].[user_id] = @userId
    AND ([tblOrders].[order_date] BETWEEN '2006/01/01' AND '2006/12/31')
    What I want to do is incorporate that into the big SQL query up top, in such a way to make it only return tickets that not only match the ticket parameters but that also aren't linked to stands, admission dates or anything else, that the user has reached their quota on.

    Oh, and I'm in a bit of a hurry so do try and get a move on won't you?

    But seriously - how do I put those two SQL querys together? Do I need one for each paramater that might have a quota attached, or is there a quicker way? All suggestions and advice, up to and including "get an easier job, dude", received gratefully
    Attached Thumbnails Attached Thumbnails schema.gif  

  2. #2
    Join Date
    Jan 2002
    Err... alternatively, any suggestions how I can rewrite the above so it gets some replies?

  3. #3
    Join Date
    Jun 2003
    Provided Answers: 1
    Well, a quick suggestion for complex business logic such as this is not to try to do it all in one SQL Query. Consider loading data from your first query into a temporary table, then use your second query to delete records that violate your quota rules, and output what is left over. This could still all be done in one stored procedure, though.
    If it's not practically useful, then it's practically useless.

    blindman "sqlblindman"

Posting Permissions

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