If I have a table where each row is a customer booking, is it OK to have 2 foreign keys in this table: one which will be null and the other referencing the primary key approriate. The reason I have two tables with the foreign key constraint applied to this table is that a booking can either be a reservation of a package of services (service_package) or a single service (service).
My guess is that this is bad design because it becomes possible to register two kinds of bookings in the same booking row, unless enforcing restrictions by using functions or stored procedures.
Is this solution OK or are there better ways to do this, like creating a more general table for the services provided?
This is a reasonable approach. The joins will fail when the the values are NULL for a particular column.
In MySQL, you have to enforce this with a trigger. Other databases have the concept of a "check constraint" where you can enforce that exactly one of the two value is populated.
If you have more columns, you might be tempted to switch to a "type" column along with an "id". This requires that all the ids have the same type. It also requires a trigger to ensure that the columns are populated correctly. And, it can lead to errors when doing joins.
Of the two alternatives, I prefer your method for just two ids.
