Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

enter image description here

share|improve this question
    
possible duplicate of Many to Many and Weak Entities –  ypercube Aug 20 '13 at 21:55

2 Answers 2

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.)

share|improve this answer

To enforce what I think you are asking for (the artist and manager should be working for the same company when an album is produced) without trigger while allowing artists and managers to move around over their careers you could try considering the artist<->manager<->label relation to be an entity which the album links too:

Artist                   ArtistManager
=============            ==============
ArtistID (PK) <--------- ArtistID  (FK)      Album
                  .----- ManagerID (FK)      ============
                  |  .-- LabelID   (FK)      AlbumID (PK)
Manager           |  |   AMCID     (PK) <--- AMCID   (FK)
==============    |  |
ManagerID (PK) <--'  |
                     |
                     |
Label                |
============         |
LabelID (PK) <-------'

Though I suspect that the requirements might be flawed: there could be multiple artists collaborating on a single album, and in this case there may be one manager or they might work under one for a that particular project, and if they might not all work for the same label at the time (cross-label projects are not completely unheard of).

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.