Help with Database structure

[Tombie]

Ok so i am creating a booking system for a series of events, however all the events use the same seating arrangements, therefore as i have it at the moment where there is only the reserved yes/no in the seating table to show if booked or not i am wondering how i would incorporate multiple events without having to duplicate the seating tables for different events as it seems a little over the top to do that. was thinking something along the lines of having a seperate events table which then links in to some new fields in the seating database which would be event1booked event2booked or something to that effect? anyway some comments would be appreciated asap thanks

[andrewgauger]

TABLE seats:

 

id  seat    eventID  booked

1  42B      1                N

2  43B      1                N

 

TABLE event:

id    name      foo    bar

1    "freaks"    no    no

 

So what you would do is

 

SELECT id, seat FROM seats WHERE eventid=1 and booked = "N"

 

if you wanted to get a list of available seats for your event that haven't been booked.

 

This is known as referential database.

 

Please review:

http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html

[ignace]

TABLE seats:

 

id  seat    eventID  booked

1  42B      1                N

2  43B      1                N

 

TABLE event:

id    name      foo    bar

1    "freaks"    no    no

 

This means that he will have to repopulate (all seats) the table seats with each new event. Instead I would use:

 

seats (id, number)

events (id, ..)

bookings (event_id, seat_id, status)

 

And the query would be a bit more complex:

 

SELECT *
FROM seats
WHERE id NOT IN (SELECT seat_id FROM bookings WHERE event_id = $eid)

-- OR --

SELECT * FROM bookings b RIGHT JOIN seats s ON b.seat_id = s.id
WHERE b.event_id = $eid AND b.seat_id IS NULL

 

This has a few advantages over andrew's solution as 1) you do not need to insert all seats on each new event and 2) no 2 end-user's can book the same seat (due to the primary key (seat_id, event_id)) a precaution against sloppy code. After all we don't know the (in)experience of the OP.

[andrewgauger]

Very nice, I concur with Ignace.

Only do an insert when something changes.

[Tombie]

TABLE seats:

 

id  seat    eventID  booked

1  42B      1                N

2  43B      1                N

 

TABLE event:

id    name      foo    bar

1    "freaks"    no    no

 

This means that he will have to repopulate (all seats) the table seats with each new event. Instead I would use:

 

seats (id, number)

events (id, ..)

bookings (event_id, seat_id, status)

 

And the query would be a bit more complex:

 

SELECT *
FROM seats
WHERE id NOT IN (SELECT seat_id FROM bookings WHERE event_id = $eid)

-- OR --

SELECT * FROM bookings b RIGHT JOIN seats s ON b.seat_id = s.id
WHERE b.event_id = $eid AND b.seat_id IS NULL

 

This has a few advantages over andrew's solution as 1) you do not need to insert all seats on each new event and 2) no 2 end-user's can book the same seat (due to the primary key (seat_id, event_id)) a precaution against sloppy code. After all we don't know the (in)experience of the OP.

 

yes thank you perfect, the first post was the way i was doing it already, and as stated i would have to have a whole new set for each event, this is perfect thank you very much, now just to incorporate the changes into my php