Resolved Needs some assistance with a seat reservation system.

[sock1992]

I'm currently creating a windows form project which is for a coach booking system using the baked in SQL server.

What I'm trying to figure out at the moment is the best way to reserve a seat for a particular customer.

In my coach table i have the coach Type i.e. single decker and double decker, and so was thinking of creating two user controls to represent a seat reservation for both. When the user has selected their booking from the grid view, one of the user controls will be called displayed on next stage of the booking process based on the type of coach that will be used for that particular journey.


This will be where the user enters journey details, and searches for an available booking:

This will be where the user selects their seat, and where it will confirm their booking details. This is just a rough layout at the moment, to give you the idea of what I'm trying to achieve:

What i want to know is how i can link the input of the seat reservation system to a particular coach schedule, so when another user creates a booking, they can see the seats that have already been taken.
Below are the three tables i currently have implemented. First one is the coach schedules that the employee would add to the system, the second is for the coach information and the third is the table that will store the details of all bookings that have been made by the customers.

Any help would be much appreciated. Thank you

 

[jmcilhinney]

You've got no foreign keys there at all, so there's no relationships among your tables. I would think that the first table should have a foreign key to the second table, i.e. a specific coach will be used to fulfil a specific schedule, and the third table should have a foreign key to the first table, i.e. a booking is made for a specific schedule. That would mean that the departingFrom, destination and dateOfJourney columns can all be removed from the last table, because they're already in the first table.

Once that's done, when a user selects a schedule to make a booking, you would get the related coach to see how many seats it has and you would get all the related bookings to see which seats had already been booked.

 

[jmcilhinney]

Another small thing to note is that you should ALWAYS strive for consistency, so your Price and CoachId columns should be named price and coachId. If someone looks at your code and sees something different to everything else, they have a right to wonder why. If there is no reason for it to be different, it shouldn't be different. If you are starting column names with a lower-case letter, start ALL column names with a lower-case letter.

 

[jmcilhinney]

Your price column would go from the booking table as well, unless it's possible to pay other than the advertised price.

 

[sock1992]

You've got no foreign keys there at all, so there's no relationships among your tables. I would think that the first table should have a foreign key to the second table, i.e. a specific coach will be used to fulfil a specific schedule, and the third table should have a foreign key to the first table, i.e. a booking is made for a specific schedule. That would mean that the departingFrom, destination and dateOfJourney columns can all be removed from the last table, because they're already in the first table.

Once that's done, when a user selects a schedule to make a booking, you would get the related coach to see how many seats it has and you would get all the related bookings to see which seats had already been booked.

Your price column would go from the booking table as well, unless it's possible to pay other than the advertised price.

Thank you so much for the feedback dude, I really appreciate it. Its helped me understand everything a lot more.