# Luminate Entity Relationships & Mapping Views

## Overview

Luminate's 6 entity types are connected through dedicated mapping views. These are essential when you need to aggregate or join data across entity levels (e.g., "total streams for all songs on an album").

All mapping views are in `LUMINATE_DB_LISTING_DETAIL.EXTRACT_S`.

## Entity Relationship Diagram

```
Artist ──(ARTISTS variant in metadata)──> Song
  │                                          │
  │                                    VW_MR_SONG_MAP_DS
  │                                          │
  │                                          v
  │                                   Musical Recording (MR/ISRC)
  │                                          │
  │                                    VW_MR_MP_MAP_DS
  │                                          │
  │                                          v
  │                                   Musical Product (MP/barcode)
  │                                          │
  │                                   VW_MP_MREL_MAP_DS
  │                                          │
  │                                          v
  │                                   Musical Release (MREL)
  │                                          │
  │                                  VW_MREL_MRELG_MAP_DS
  │                                          │
  │                                          v
  └──(ARTISTS variant in metadata)──> Musical Release Group (MRELG/album)
                                             ^
                                             │
                                    VW_SONG_MRELG_MAP_DS
                                             │
                                           Song
```

## Mapping Views

### VW_MR_SONG_MAP_DS — Musical Recording ↔ Song
- Join keys: `MR_ID`, `SONG_ID`
- Cardinality: Many-to-many (a song can have multiple recordings; a recording can map to multiple songs, though rare)
- Use case: Aggregating recording-level consumption up to the song level

### VW_MR_MP_MAP_DS — Musical Recording ↔ Musical Product
- Join keys: `MR_ID`, `MP_ID`
- Also includes: `ISRC`, `ICPN` (barcode)
- Cardinality: Many-to-many (a recording appears on multiple products; a product contains multiple recordings)
- Use case: Finding which products contain a specific ISRC, or listing all tracks on a product

### VW_MP_MREL_MAP_DS — Musical Product ↔ Musical Release
- Join keys: `MP_ID`, `MREL_ID`
- Cardinality: Many-to-many
- Use case: Connecting product-level data to release metadata

### VW_MREL_MRELG_MAP_DS — Musical Release ↔ Musical Release Group
- Join keys: `MREL_ID`, `MRELG_ID`
- Cardinality: Many-to-many (an album can have multiple releases — deluxe editions, regional variants)
- Use case: Rolling up release data to the album level

### VW_SONG_MRELG_MAP_DS — Song ↔ Musical Release Group (shortcut)
- Join keys: `SONG_ID`, `MRELG_ID`
- Cardinality: Many-to-many
- Use case: **Shortcut** to go directly from Song to Album without traversing MR → MP → MREL → MRELG. Use this when you need song-to-album mapping without intermediate detail.

## Airplay Mapping Views

### VW_STATION_FORMAT_MAP_DS — Station ↔ Airplay Format
- Join keys: `STATION_ID`, `FORMAT_ID`
- **Important**: This mapping is point-in-time. Use `REPORT_DATE` to get the correct station-to-format assignment for the period being analyzed.
- Not all stations are mapped to a format. Unmapped stations should be grouped into a generic category (e.g., "Others").

### VW_SONG_AIRPLAY_FORMAT_DS — Song CRG Status per Format/Week
- Join keys: `SONG_ID`, `FORMAT_ID`, `WEEK_ID`
- Contains `CRG` field: Current, Re-current, or Gold status
- `CRG_CLIENT_DOMAIN`: Whether the CRG assignment follows Luminate or Billboard methodology
- Point-in-time: varies by week

## Artist Associations

Artist linkage to other entities is not done via a separate mapping view. Instead, it is stored as a `VARIANT` (JSON) column in the metadata views:
- `VW_MUSICAL_RECORDING_DS.ARTISTS` — array of artists on the recording
- `VW_SONG_DS.ARTISTS` — array of artists on the song
- `VW_MUSICAL_RELEASE_GROUP_DS.ARTISTS` — array of artists on the album

To join fact data to artist metadata, use `VW_ARTIST_DS` joined via `ARTIST_ID`, which is a direct key on artist-level fact views (`VW_DAILY_FACT_ARTIST_*`).

## Common Join Patterns

**Song streams rolled up to album level:**
```sql
-- Use the shortcut mapping view
SELECT m.MRELG_ID, SUM(f.QUANTITY) AS total_streams
FROM LUMINATE_DB_LISTING_DETAIL.EXTRACT_S.VW_DAILY_FACT_SONG_SUMMARY_DS f
JOIN LUMINATE_DB_LISTING_DETAIL.EXTRACT_S.VW_SONG_MRELG_MAP_DS m
  ON f.SONG_ID = m.SONG_ID
WHERE f.COUNTRY_CODE = 'US'
  AND f.METRIC_CATEGORY = 'Streams'
  AND f.REPORT_DATE BETWEEN '2024-01-01' AND '2024-01-07'
GROUP BY m.MRELG_ID;
```

**Find all ISRCs on a specific barcode:**
```sql
SELECT m.MR_ID, m.ISRC, m.MP_ID, m.ICPN
FROM LUMINATE_DB_LISTING_DETAIL.EXTRACT_S.VW_MR_MP_MAP_DS m
WHERE m.ICPN = '<barcode_value>';
```

**Top artists by streams (fact-to-metadata join):**
```sql
SELECT
    f.ARTIST_ID,
    a.DISPLAY_ARTIST,
    SUM(f.QUANTITY) AS total_streams
FROM LUMINATE_DB_LISTING_DETAIL.EXTRACT_S.VW_DAILY_FACT_ARTIST_SUMMARY_DS f
JOIN LUMINATE_DB_LISTING_DETAIL.EXTRACT_S.VW_ARTIST_DS a
  ON f.ARTIST_ID = a.ARTIST_ID
WHERE f.COUNTRY_CODE = 'US'
  AND f.METRIC_CATEGORY = 'Streams'
  AND f.REPORT_DATE BETWEEN '2024-01-01' AND '2024-01-07'
GROUP BY f.ARTIST_ID, a.DISPLAY_ARTIST
ORDER BY total_streams DESC
LIMIT 100;
```

## Pitfalls

- **Many-to-many relationships**: Joining through mapping views without careful aggregation can multiply rows. Always aggregate at the correct level.
- **Artist linking**: Don't try to join artists to songs via a mapping view — use the `ARTISTS` variant column in metadata or query artist-level fact views directly.
- **Airplay station formats change over time**: Always include REPORT_DATE when joining VW_STATION_FORMAT_MAP_DS.