Skip to main content
Submitted by gill on

To properly link recordings to concerts, and thus tie each recording to the actual concert performance where it occurred, you'll want to:

  1. Replace the ambiguous concert and venue fields (currently just strings) with a foreign key reference to the concerts table.
  2. Optionally retain or rename other fields (like venue) only if they are recording-specific and not redundant with what's in concerts.

✅ Updated recordings Table Definition

Here is the improved version of the recordings table:

DROP TABLE IF EXISTS `recordings`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;

CREATE TABLE `recordings` (
 `id_recording` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Unique ID for this recording',
 `catalog_number` varchar(5) NOT NULL COMMENT 'Catalog number of the composition',
 `id_concert` int(11) NOT NULL COMMENT 'Which concert this recording is from',
 `name` varchar(255) DEFAULT NULL COMMENT 'Name of the piece or excerpt on the recording',
 `ensemble` varchar(2048) DEFAULT NULL COMMENT 'Ensemble or performer name',
 `link` varchar(512) DEFAULT NULL COMMENT 'URL or path to the audio or video recording',
 `composer` varchar(255) DEFAULT NULL COMMENT 'Composer for labeling purposes',
 `arranger` varchar(255) DEFAULT NULL COMMENT 'Arranger for labeling purposes',
 `enabled` int(11) NOT NULL DEFAULT 0 COMMENT 'Enable flag for display or availability',

 PRIMARY KEY (`id_recording`),
 KEY `catalog_number` (`catalog_number`),
 KEY `id_concert` (`id_concert`),

 CONSTRAINT `recordings_ibfk_1` FOREIGN KEY (`catalog_number`) REFERENCES `compositions` (`catalog_number`) ON DELETE CASCADE,
 CONSTRAINT `recordings_ibfk_2` FOREIGN KEY (`id_concert`) REFERENCES `concerts` (`id_concert`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci COMMENT='This table keeps recordings.';
/*!40101 SET character_set_client = @saved_cs_client */;
 

🔍 Changes Made:

  • ✅ Added id_concert: This links the recording directly to a concert.
  • 🗑 Removed concert and venue as free text: This avoids duplication since those values are in the concerts table.
  • 🔄 Kept fields like composer, arranger, and ensemble: Useful for labeling and searchability, even if technically redundant.

💡 Bonus Query Idea:

Get all recordings from a concert:

SELECT  r.name AS recording_name,  r.link,  c.performance_date,  c.venue,  p.title AS program_title FROM recordings r JOIN concerts c ON r.id_concert = c.id_concert JOIN playgrams p ON c.id_playgram = p.id_playgram WHERE c.performance_date = '2025-06-09';

Let me know if you’d like to normalize composer/arranger into their own tables or include indexing suggestions!

Great — since you're transitioning from a flat recordings table to a normalized schema using concerts, we’ll write a migration query in two parts:

✅ 1. Insert into concerts from old recordings table

We'll generate unique concerts based on combinations of date, venue, and a placeholder or derived playgram.

If you're not yet assigning a real playgram, you can insert a dummy one and update later.

-- 1a. Create a dummy playgram (if none exist)
INSERT IGNORE INTO playgrams (title, description)
VALUES ('Imported Legacy', 'Auto-generated for imported recordings');

-- 1b. Get its ID
SET @default_playgram_id = (
   SELECT id_playgram FROM playgrams WHERE title = 'Imported Legacy'
);

-- 1c. Insert unique concert entries from old recordings
INSERT INTO concerts (id_playgram, performance_date, venue, conductor, notes)
SELECT DISTINCT
   @default_playgram_id AS id_playgram,
   `date` AS performance_date,
   `venue`,
   'Unknown' AS conductor,
   CONCAT('Imported from recording entry: ', `name`) AS notes
FROM recordings
WHERE `date` IS NOT NULL AND `venue` IS NOT NULL;
 

✅ 2. Insert into the new recordings table using concerts.id_concert

You’ll now map each legacy recording row to its matching concert.

INSERT INTO recordings (
   catalog_number,
   id_concert,
   name,
   ensemble,
   link,
   composer,
   arranger,
   enabled
)
SELECT
   r.catalog_number,
   c.id_concert,
   r.name,
   r.ensemble,
   r.link,
   r.composer,
   r.arranger,
   r.enabled
FROM recordings r
JOIN concerts c
 ON r.date = c.performance_date
 AND r.venue = c.venue;
📝 Notes:
  • This assumes every (date, venue) combo is unique per concert.
  • The dummy playgram can be updated later with real content and linked appropriately.
  • You can verify how many concerts were created:

 

SELECT COUNT(*) FROM concerts WHERE id_playgram = @default_playgram_id;

 

Here’s a safe rollback script that undoes the migration from the old recordings table to the new concerts and normalized recordings structure — specifically the data we just inserted based on the "Imported Legacy" placeholder.

✅ What This Does

  • Step 1 retrieves the ID of the dummy playgram ("Imported Legacy").

  • Step 2 deletes all recordings that were linked to concerts using that playgram.

  • Step 3 deletes the concerts themselves.

  • Step 4 removes the dummy playgram.

This approach only removes what was inserted by the migration process, keeping your original recordings table untouched.

🔁 Rollback Script

-- Step 1: Get the playgram ID used for imported legacy data
SELECT @imported_playgram_id := id_playgram
FROM playgrams
WHERE title = 'Imported Legacy';

-- Step 2: Delete recordings that reference concerts from that playgram
DELETE FROM recordings
WHERE id_concert IN (
    SELECT id_concert
    FROM concerts
    WHERE id_playgram = @imported_playgram_id
);

-- Step 3: Delete the concerts tied to that playgram
DELETE FROM concerts
WHERE id_playgram = @imported_playgram_id;

-- Step 4: Finally, delete the placeholder playgram itself
DELETE FROM playgrams
WHERE id_playgram = @imported_playgram_id;
Private
No