Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2010
    Posts
    2

    Question One relation defines another one?

    Hi,

    I am currently struggling with a redundancy, I cannot get rid of.
    I am trying to create a small ticketing-system.
    You can see the current design here

    There is one redundancy involved:
    The relation between Events and Locations defines, which Seats can be booked per Event. But there is no mechanism the enforces the Ticket to belong to a seat that is part of its event.
    In other words: It might happen that there is a ticket in the system that belongs to a seat that is not part of the location the event belongs to.

    I don't have the slightest idea, how to resolve that. Do you?

    Thank you very much for an answer
    Andreas
    Attached Thumbnails Attached Thumbnails 2010-09-14 22-47-53.png  

  2. #2
    Join Date
    May 2008
    Posts
    277
    Start with location:

    location (location_id*)

    Each location has multiple seats, each seats belongs to one location. Since we don't expect seats to ever change locations, the key of location is taken as part of the key of seat:

    seat (location_id*, seat_id*)

    For the purposes of this example, we'll assume that an event takes place at one location. Obviously, a location will have multiple events. The structure is similar to seats:

    event (location_id*, event_id*)

    A ticket, then, is basically an association table relating a seat to an event:

    ticket (seat_location_id*, seat_id*, event_location_id*, event_id*)

    Since the seat location and event location must be the same, you can simplify this to:

    ticket (location_id*, seat_id*, event_id*)

  3. #3
    Join Date
    Sep 2010
    Posts
    2
    Quote Originally Posted by futurity View Post
    Start with location:
    location (location_id*)
    seat (location_id*, seat_id*)
    event (location_id*, event_id*)
    ticket (location_id*, seat_id*, event_id*)
    Thank you for your answer
    I got your idea on the second glance. Took me a while to see that you are creating the event- and seat-ids per location.

    I guess, there is no way to solve this with simple "one-column-keys"?
    Last edited by AndiHoffi; 09-15-10 at 04:00.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Quote Originally Posted by AndiHoffi View Post
    I guess, there is no way to solve this with simple "one-column-keys"?
    No easy way, no. This is one reason why "simple one-column keys" aren't always such a neat idea.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by andrewst View Post
    No easy way, no.
    I would be interested how this requirement could be modelled with surrogates - do you have something in mind please Tony? I have come across such a requirement a handful of times and have never been able to model it any way other than futurity's method.

    I had considered writing up an abstract of such a problem to present to the "surrogates and only surrogates" brigade and ask them how they would solve it but of course I couldn't be arsed.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Quote Originally Posted by pootle flump View Post
    I would be interested how this requirement could be modelled with surrogates - do you have something in mind please Tony? I have come across such a requirement a handful of times and have never been able to model it any way other than futurity's method.
    Not really. In theory, if any DBMS actually supported ANSI "assertions" you could use one of those - something like:
    Code:
    create assestion a1 check 
    (select count(*)
     from ticket t
     join seat s using (seat_id)
     join event e using (event_id)
     where s.location_id != e.location_id) = 0;
    ... but of course they don't. Another possibility is materialized views with constraints - but this is an academic solution rather than one to put into production, perhaps.

    In practice it would probably be done using triggers, which is a bit sucky.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Not heard of assertions, but that could be done in SQL Server using a CHECK constraint checking the return of a scalar function.

    But all three options you've come up with are, as you imply, inferior to simply designing the thing properly with natural keys. Thanks for the response
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Jul 2009
    Location
    Michigan
    Posts
    125
    When you insert a row into the Events table a trigger or stored procedure should insert into a Event/Seat table that contains the EventID, SeatID, and a flag that indicates availability (available, sold, reserved). You may want to add price depending on how you are doing pricing. The Ticket should be linked to rows in this table. This way you know what Seats are still available for each event AND you are sure the Seat actually ties to that Event.
    Last edited by MarkATrombley; 09-17-10 at 13:58.

Posting Permissions

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