2 Replies Latest reply on Apr 15, 2015 9:29 AM by EdwardWinslow

    Unique Identifier from Two Tables



      Unique Identifier from Two Tables



      I have a database of ticket sales I need help with.  We have a table for Events, Contacts, and Participations (contact ID, Event ID, and a unique ParticipationID).  Then I have another table for Guests linked to Participation.  That means that Participants can bring Guests to an event.  All's well so far.

      I then created another table called Seat List, whose purpose was to maintain a list of seats at the various theaters we use, and sometimes use the seat list for temporary seating arrangements at an event.  The goal is to limit the number of seats and put guests in the seats next to their friends and the organizations they represent.  The Seating ID is unique in both tables and there can only be one guest in a seat and if I try to put two guests in a seat the program spits back a revert field contents error message. All's still well.

      It works great for a year, but when another year comes up and I want to do the event again I would rather not create a whole new seat list because chances are they will be sitting at the same seat (or a different one) and the database will show the seat as occupied.

      Could I add the year or EventID to the SeatID to create a new composite unique identifier?  Can a calculation like that be indexed and unique?  The SeatID in the Guest List must relate to the Seat List so I can show the Guest a card with their Region, Section, Table, and Seat.



        • 1. Re: Unique Identifier from Two Tables

          Some questions...Is the same event always held at the same theatre, year after year?  Although it may be the case now, it may not always be the case in the future.

          Just guessing, and I'm sure I'm not entirely right, but I think I would link the seat table (really a Venue Table) to each event thru a join table, in the same way you have an Invoice<LineItems>Products table set up. 




            This way when you create a new Event, you select the Venue.  The venue provides the seating chart.  When you pick participants, and their guests, you assign them seats.  Then every event, has a venue and the appropriate seating chart.  Just not sure without playing with it, how you select contacts/guests with the seats

          At the very least I gave you a bump, hopefully someone else can add on/correct me, or have a completely different idea.

          • 2. Re: Unique Identifier from Two Tables

            Thanks Steve,

            I do have a venue table set up, and that works well.  The seat list is rather complex to create, and I would prefer to retain it year after year when guests come back to sit in the same seats next time.  We make a distinction between Contacts (event sponsors, performers, and organizations) and guests (invitees to the event).  So the format of the table structure would more accurately be Events<Participation>Contacts and Participation<Guests> and Guests-Seat List and Venue<Seat List> and Events-Venue

            With regards to my problem, I have created a composite field that combines SeatID and EventID in the Guest List through a calculation, but I can't define that field as unique because it can't be stored and indexed since it references a parent table.  I could have a script create the composite key whenever the seatingID is updated but that seems like a rather convoluted solution.