How to create one-to-zero-or-one relationship programmatically - MySQL?

Question

I'm busy with a project and one of the stumbling blocks I've come across has been the following:

I have a bookings table, which may or may not result in an invoice being issued (because of some irrelevant stuff such as cancellations). How would I enforce a one (on the bookings side) to zero-or-one (on the invoice side) relationship? Here's what I have thus far:

CREATE TABLE IF NOT EXISTS `booking` (    
   `booking_id` int(11) NOT NULL AUTO_INCREMENT,   
   `voucher_id` int(11) NOT NULL,  
   `pickup_date_time` datetime NOT NULL, ...  
   PRIMARY KEY (`booking_id`,`voucher_id`)  
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

And then, later on:

  CREATE TABLE IF NOT EXISTS `invoice` (  
  `booking_id` int(11) NOT NULL,  
  `voucher_id` int(11) NOT NULL,  
  `invoice_number` int(11) NOT NULL,  
  `paid` tinyint(1) NOT NULL,  
  PRIMARY KEY (`booking_id`,`voucher_id`),  
  UNIQUE KEY `invoice_number` (`invoice_number`),  
  KEY `voucher_id` (`voucher_id`)  
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

voucher_id is just something else I use in the system. The invoice_number is also generated in the PHP, so this is irrelevant.

Any help would be greatly appreciated!

I should add that I hate any nullable values. So anything that avoids NULLs will be awesome! — iLikeBreakfast, Apr 14 '13 at 14:55
Just curious, why isn't `invoice_number` the PK and `booking_id`,`voucher_id` a UNIQUE? Seems a bit illogical, your foreign keys will probably point to invoice_number? — thaJeztah, Apr 14 '13 at 15:13
Also, the compound key in bookings - this implies I can have the *same* `booking_id` **twice** as long as I enter a different *voucher_id*? You should make `booking_id` the PK and add a UNIQUE constraint on `voucher_id` to enforce that a voucher can only be used once. OR bring 'booking_id' to the vouchers table (yes this will be nullable) I.e. vouchers that are not yet used will have `null` as booking_id and an existing booking_id once they have been used. — thaJeztah, Apr 14 '13 at 15:15
@thaJeztah yes that compound key is fine like that I think - many bookings may be made with the same voucher number, and the `booking_id` will be an auto increment field, so it won't really ever _repeat_ as such. The naming conventions are very confusing I admit, but out of a DB perspective, does what I'm saying make sense? — iLikeBreakfast, Apr 14 '13 at 15:32
No it doesn't; first of all, AutoNumber is just to *assist* creating a new number, it doesn't prevent you from manually inserting a record with a booking_id, which will allow duplicated booking_ids. Furthermore; is a voucher *required* to make a booking? And finally, if you want 'gapless' booking-ids (I.e. *don't* want 0001, 0005, 0006), don't rely on AutoNumber either, because a failed insert may also increment the counter. IMO, `booking_id` is the PK, `voucher_id` is a foreign key for 'vouchers' (and should be nullable if a voucher is not required for a booking) — thaJeztah, Apr 14 '13 at 15:50
Ah ok I see your point. Implemented it like that. Thanks for the help! — iLikeBreakfast, Apr 14 '13 at 16:17
Also mark my first comment; the same applies to invoices; PK should be `invoice_id` and now that the PK for bookings is `booking_id` you can remove the `voucher_id` column. One final thing to consider; if a booking can have multiple vouchers applied (e.g. If a voucher is used to get a discount, and combining them is allowed) then store them as a n:m relation (HasAndBelongsToMany) inside a join table `bookings_vouchers` — thaJeztah, Apr 14 '13 at 16:26
Yeah I see your points regarding the `voucher_id` that could be removed. Won't do the `bookings_vouchers` thing, seeing as one voucher can be used for many bookings and not the other way around. Thanks for all the help! — iLikeBreakfast, Apr 14 '13 at 16:42
Glad I could give you some pointers in the 'right' direction. Nothing is ever written in stone, it really depends on your use, but I think these changes are correct in any situation — thaJeztah, Apr 14 '13 at 18:22

score 5 · Accepted Answer · answered Apr 15 '13 at 02:16

This is more-less just a systematization of what @thaJeztah already suggested in his comments, but here you go anyway...

CREATE TABLE voucher (
    voucher_id int(11) PRIMARY KEY
   -- Etc...
);

CREATE TABLE booking (
    booking_id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    voucher_id int(11) REFERENCES voucher (voucher_id),
    pickup_date_time datetime NOT NULL
   -- Etc...
);

CREATE TABLE invoice (
    invoice_number int(11) NOT NULL PRIMARY KEY,
    booking_id int(11) NOT NULL UNIQUE REFERENCES booking (booking_id),
    paid tinyint(1) NOT NULL
   -- Etc...
);

Minimal cardinality: There can be a booking without an invoice. There cannot, however, be an invoice without the booking (due to the FK on the non-NULL field invoice.booking_id).

Maximal cardinality: A booking cannot be connected to multiple invoices due to the UNIQUE constraint on invoice.booking_id. An invoice cannot be connected to multiple bookings, simply because one field (in one row) cannot contain multiple values.

So, the resulting relationship between booking and invoice is "one to zero or one".

Alternatively, put everything in just one table with NULL-able fields that get progressively filled as the booking advances.

Is there generally a reason for using two tables as opposed to one with nullable fields, or is it simply personal preference? — kcstricks, Jun 10 '16 at 03:30
@kcstricks In this particular case, no. However, if this is a part of a larger database model, you may want to keep the independent tables, so you can more easily connect them to the tables from the rest of the model. There may also be some physical considerations - this [answer](http://stackoverflow.com/a/9688442/533120) lists some of these concerns for a true 1:1 relationship, but some of them may be applicable to 1:0..1 as well... — Branko Dimitrijevic, Jun 10 '16 at 07:10

How to create one-to-zero-or-one relationship programmatically - MySQL?

1 Answers1