Table Structure

[Stephenlyn]

Hi, I'm developing a bookings database for a school auditorium.<br><br>I've set it up so amongst other tables I have three major tables, Customer, Bookings and seats.<br><br>The Customer is self explanatory, the booking relates to a performance ID and seats is the seat allocation for a particular performance.<br><br>The processing sequence is register a customer, book that customer for a performance then allocate a seat/s for that performance to that customer.<br><br>The way I've set the seats up is to have 700 radio buttons in 6 tables with the SK being the performance ID. <br><br>I'm having a few problems getting it to work, such as linking the customer to the performance and then the seats.<br><br>Now the Question.&nbsp;&nbsp;&nbsp;Does this sound OK, am I heading in the right direction or am I way of track?<br><br>Thanks<br>Steve

 

[HIM]

I would probably have done something like this:<br><br>Customer - Table<br>ID<br>Name<br>etc.<br><br>Event - Table<br>ID<br>Name<br><br>EventSeats - Table<br>Event (lookup from Event)<br>Seat<br><br>Booking - Table<br>ID<br>Event (lookup from event)<br>Customer (lookup from customer)<br><br>BookingSeats - Table<br>Booking (Foreign key (booking))<br>Seat (lookup from EventSeats)<br><br>Do you get the picture? Otherwise, mail me at <A HREF="mailto:hhmoeller@image.dk">hhmoeller@image.dk</A>

 

[Stephenlyn]

A clarification I have is: in the Bookings seat&nbsp;&nbsp;Table you say lookup seat from event seats.&nbsp;&nbsp;&nbsp;the problem I have is it says a maximum of 20 for a lookup, I have 700 seats.<br>

 

[Stephenlyn]

The next problem I have is that the seats table can only have 255 fields... do I only fill a third of the auditorium?

 

[gol4]

You dont want to use a field for each seat. Tables should look something like this <br><br>Seat Table<br>seatid&nbsp;&nbsp;section&nbsp;&nbsp;seat<br>A-1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;A&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1<br>A-2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;A&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2<br>..................<br>Z-36&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Z&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;36&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;event table<br>eventid&nbsp;&nbsp;&nbsp;event&nbsp;&nbsp;&nbsp;&nbsp;date&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;sttime&nbsp;&nbsp;&nbsp;endtime<br>&nbsp;&nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;prom&nbsp;&nbsp;&nbsp;&nbsp;5/1/2000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;08:00&nbsp;&nbsp;&nbsp;&nbsp;16:00<br>&nbsp;&nbsp;2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;meeting 5/2/2000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;07:00&nbsp;&nbsp;&nbsp;&nbsp;13:00<br><br><br>customer<br>custid&nbsp;&nbsp;name&nbsp;&nbsp;etc..<br>&nbsp;&nbsp;&nbsp;&nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;jones <br>&nbsp;&nbsp;&nbsp;&nbsp;2&nbsp;&nbsp;&nbsp;&nbsp;smith<br>&nbsp;&nbsp;&nbsp;&nbsp;3&nbsp;&nbsp;&nbsp;&nbsp;young<br><br>booking table<br>eventid&nbsp;&nbsp;seatid&nbsp;&nbsp;custid<br>&nbsp;&nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;A-1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1<br>&nbsp;&nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;c-23&nbsp;&nbsp;&nbsp;&nbsp;3&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;A-1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2<br>&nbsp;

 

[elizabeth]

I would suggest a slightly different table structure than HIM:<br><br>tblCustomer: Customer ID, etc.<br>tblBooking: BookingID, etc.<br>tblSeat: SeatID, SectionID, SeatNo (as shown by gol4 - or add RowID if needed)<br>tblTicket:TicketID, CustomerID, BookingID, SeatID<br><br>I would suggest you use a text field with leading zeros for your seat number so that you can sort and select by section and seatid for more flexibility, and then combine them programmatically as needed. (seatid = section & seat)and then sort on it. if you use A2 and A11, A11 will sort <i>before</i> A2. But A11 would sort <i>after</i> A02.<br><br>Good luck

 

[Stephenlyn]

...and then combine them programmatically as needed. (seatid = section & seat)<br><br>How do I make the seatID get the values from section & seat?<br><br>regards<br><br>Steve

 

[elizabeth]

Just like it looks. Here's how I do a similar function, concatenating building code, floor code and room code to create a location code, in the form's before update event. I use the hyphens in between to make it more readable.<br><br>Private Sub Form_BeforeUpdate(Cancel As Integer)<br>&nbsp;&nbsp;&nbsp;&nbsp;Me!LocationCode = Me!BuildingCode & &quot;-&quot; & Me!FloorCode & &quot;-&quot; & Me!RoomCode<br>End Sub<br><br>Of course I'm previously ensuring that valid values are in those fields before I even get here.

 

[Stephenlyn]

Thanks Elizabeth et al I've done all that, but I can't seem to work out the next step...that is Ive got a customer form filled in all details, called the bookings form for that customer, booked a performance filled in date number of tickets price etc... now how do I allocate 'n' seats for that performance to that customer.<br><br>Your help would be much appreciated and make this little Aussie very happy.<br><br>Steve

 

[elizabeth]

You may need two methods; one for when the customer requests a specific section &/or row &/or seat, and one for when a customer asks for a large block specifying only section &/or row. Fr either method, start with 3 comboboxes. First is all sections, second is all rows (limited by selected section), third is all seats (limited by selected section &/or row, don't know how your facility is set up. For someone who wants a specific seat or 2, just use the combos to select. When they want a block, I <i>think</i> you would create a record set of the slected criteria, then loop through with a loop counter looking for and assigning available seats until you reach the loop limit that equals the number of tickets selected. Maybe someone else could give you an alternate idea or more details on that.

 

[Stephenlyn]

let me see, have I got this right.<br>Ive got a number of combo boxes, one with rowID eg AA and another with seatNo eg 32.&nbsp;&nbsp;So that's one seat allocated AA32.&nbsp;&nbsp;&nbsp;Do I then click a button to save that seat then select another seat? or....<br><br><br>regards