Guaranteeing a FK relationship through multiple tables I'm using MySQL / InnoDB, and using foreign keys to preserve relationships across tables. In the following scenaro (depicted below), a 'manager' is associated with a 'recordLabel', and an 'artist' is also associated with a 'recordLabel'. When an 'album' is created, it is associated with an 'artist' and a 'manager', but both the artist and the manager need to be associated with the same recordLabel. How can I guarantee that relationship with the current table setup, or do I need to redesign the tables?
The relationship, as presented in the question, could be enforced with a simple trigger.  This example relies on SIGNAL from MySQL 5.5 and later, to throw an exception and prevent the insertion of invalid data.

DELIMITER $$
DROP TRIGGER IF EXISTS albums_bi $$
CREATE TRIGGER albums_bi BEFORE INSERT ON albums FOR EACH ROW
BEGIN

  IF (SELECT record_label_id FROM artist a WHERE a.id = NEW.artist_id) !=
     (SELECT record_label_id FROM manager m WHERE m.id = NEW.manager_id) THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'artist and manager must be from the same label';
  END IF;

END $$
DELIMITER ;


That's the simple version.  You'd also need a BEFORE UPDATE trigger to accomplish the same thing, preventing records from being subsequently changed to be invalid.

I've used the word "simple" twice, now, and we all know that word doesn't really belong in discussion of databases...

What about the case where an artist or a manager changes record labels?  Are the albums no longer valid?  Since this is presumably historical data over time, and presumably artists and/or managers may have moved from one label to another, it doesn't seem to make a lot of sense to have quite such a static model.

To model this data accurately, it almost seems like you'd need some kind of temporal tables that reflected the period of time in the real world when the facts stated in the database were true.

For example, an "artist_recording_label_contract" table for the periods of time during which an artist had a contractual relationship with a recording label... with something like artist_recording_label_contract_id (surrogate pk identifying the relationship between an artist and a recording label for one specific window of time), artist_id, record_label_id, start_date, end_date ... then another similar table with the same information for managers... and each album would reference the respective record in each of these time window tables when the artist and manager worked for the same label.

Then your trigger would have to validate that the artist and the manager's contract windows were not only with the same recording label, but also during an overlapping window of time... which might also need to overlap with a date in the album table if you're tracking when albums were produced.

And you'd need to potentially prevent data in the contract window tables from being updated in such a way that would invalidate any associated albums or cause windows to improperly overlap with each other, such as a manager working for the same label "from 1971 to 1976" and "from 1975 to 1980."

(So much for simple.)