MySQL making a booking 3 PK's required

[loopylou8]

Hi,
I'm wondering if anyone can help me with a query? I have 3 tables in phpmyadmin, vehicles, drivers, and bookings.

I need to make a booking in the bookings table (primary keys are DriverID, VehicleID, BookingDate) this will ensure its a unique booking and the DriverID, VehicleID need to be checked as valid from the other tables. (if there is no driverID record, you can't make a booking, no vehicleID...you can't make a booking) there will also be a requirement to make sure I don't have a double booking but I'm not worried about that right now!

I can see its a select statement with where and "and" conditions... but I'm blank as to what it would be. I'd really appreciate any help :)

Thanks in advance from newbie :)

[smantscheff]

What are "3 PK's"?
You can avoid trouble by setting foreign keys in the bookings table. These make sure that you cannot enter a booking with invalid IDs. And set a unique key on the driver/car combination to avoid double bookings. Like that:

create table bookings (driverID integer not null, vehicleID integer not null,
foreign key (driverID) references drivers(driverID),
foreign key (vehicleID) references vehiclles(vehicleID),
unique (driverID,vehicleID)
);

To avoid invalid and double entries in the interface, you have to query if the values you want to enter are already in the table.
You can put it all in one query like this:

select d.driverID, v.vehicleID 
from drivers d, vehicles v
where d.driverID=$newDriverId and v.vehicleID=$newVehicleId
and not (d.driverID,v.vehicleID in (select driverID,vehicleID from bookings));

If this query has a result row, you may use $newDriverId and $newVehicleId for a new booking.

[loopylou8]

Hi,
Thank you for responding,
3 pk's = 3 primary keys - to create a unique booking.

I'm unfamiliar to working with mysql and php so please bear with me if I'm not too quick on "catching on"

I can see that I need to do some sort of query of the drivers table & vehicles table to make sure that there is a valid id for both driver and vehicle but can that be done at the time of doing the Insert into Bookings....? My head is so muddled by this!

thanks

[smantscheff]

General advice: use the mysql command line client for developing and testing your queries. You will be less prone to php and programming logic errors.
You do not need 3 but only 2 primary keys: driverID and vehicleID. Both together must be unique in the bookings table, therefore the unique index.
If you set up the tables as described above (with foreign key checks) the database will take care that you cannot enter invalid values. But if you try it leads to a database error message which you don't want to show to your users.
Therefore you have to check first if the desired values are valid which you can do with the above query.
You cannot have the checking and the insert in one single statement.
If sone time may pass between checking and the insert and invalidate the checking result, you will have to bracket the check and the insert statement into a transaction.

You could also insert data with the INSERT IGNORE statement which does not throw an error on duplicate keys. You can afterwards check the number of "affected rows" by this insert query - if it is 0, no data have been inserted.

[loopylou8]

I think this is just a bit above what I've learnt so far, thanks for taking the time to try and explain it.
I'll have another look in the morning - with fresh eyes!

Cheers

[drjohn]

Sorry, but if just driverID and vehicleID are the joint primary key OR are a unique pairing, then no driver can ever drive the same vehicle more than once. Ever. Therefore the suggestion above is just WRONG.

As you originally had it, a triple joint primary key was the correct solution. That way the same driver and same vehicle can be booked but it must be on a different date. Assuming that a driver and vehicle are never booked for two different things on the same day, that is.

If you have separate tables for driver (and you should) and vehicle (and you should) AND for booking (and you probably should) then the bit about foreign keys does prevent you using a driver who doesn't exist and a vehicle which doesn't exist. That's what foreign keys are for. Assuming of course that you state the correct storage engine type because the default engine in MySQL is myisam, which does not support the concept of foreign keys. You must make it use the innodb engine for foreign key referential integrity.

Triple joint keys are less common than double ones, but they do occur.

How would you check that the driverID was valid - only offer valid driverIDs via a drop-down list, and ditto for vehicleID.

And how do you get these to be valid - start with a form where the date is entered first, clicking the NEXT button then queries the database for drivers and vehicles which are not booked on that day, then uses the results of these two queries to populate the drop-down selections. OR tells you that no driver or vehicle is available, which is equally useful to know.

But. This does assume that a booking is only for one day. If a booking can span several days, there is a more complicated way to check for overlaps.

And of course this approach also means that you can't create a double booking because firstly you are only retrieving drivers and vehicles which are not already booked and secondly the triple joint primary key automatically prevents the record being saved. AND this approach can actually work without the foreign key restraints as well, because you are querying the database to FIND the existing drivers and vehicles.

[loopylou8]

Sorry, but if just driverID and vehicleID are the joint primary key OR are a unique pairing, then no driver can ever drive the same vehicle more than once. Ever. Therefore the suggestion above is just WRONG.

As you originally had it, a triple joint primary key was the correct solution. That way the same driver and same vehicle can be booked but it must be on a different date. Assuming that a driver and vehicle are never booked for two different things on the same day, that is.

If you have separate tables for driver (and you should) and vehicle (and you should) AND for booking (and you probably should) then the bit about foreign keys does prevent you using a driver who doesn't exist and a vehicle which doesn't exist. That's what foreign keys are for. Assuming of course that you state the correct storage engine type because the default engine in MySQL is myisam, which does not support the concept of foreign keys. You must make it use the innodb engine for foreign key referential integrity.

Triple joint keys are less common than double ones, but they do occur.

How would you check that the driverID was valid - only offer valid driverIDs via a drop-down list, and ditto for vehicleID.

And how do you get these to be valid - start with a form where the date is entered first, clicking the NEXT button then queries the database for drivers and vehicles which are not booked on that day, then uses the results of these two queries to populate the drop-down selections. OR tells you that no driver or vehicle is available, which is equally useful to know.

But. This does assume that a booking is only for one day. If a booking can span several days, there is a more complicated way to check for overlaps.

And of course this approach also means that you can't create a double booking because firstly you are only retrieving drivers and vehicles which are not already booked and secondly the triple joint primary key automatically prevents the record being saved. AND this approach can actually work without the foreign key restraints as well, because you are querying the database to FIND the existing drivers and vehicles.

Wow, thanks for that!
and amazing I did follow almost all of what it means too!:-O
I'm going to leave for tonight, but will look at it tomorrow,

many thanks,:)