【数据分析面试】21.Spotify 音乐数据库搭建（SQL主键和外键）

题目：音乐数据库设计

假设现在你在Spotify工作，你需要设计一个用于存储歌曲元数据的关系型数据库。

数据库要求包括歌曲标题、歌曲长度、歌曲添加到平台的日期、艺术家、专辑、曲目号（在专辑中的顺序）、歌曲的发行年份以及流派等元数据。

你会如何设计这个数据库？如果还需要设计用户数据库呢？

公司背景介绍
Spotify是一家总部位于瑞典的音乐流媒体服务提供商。它提供了一个在线音乐平台，用户可以通过该平台播放数百万首歌曲、播客和其他音频内容。

答案

1. 歌曲表（Songs table）作为歌曲元数据的中心点。歌曲表中的每一项对应一个单独的歌曲，并包括诸如标题、长度、发行日期等详细信息。每首歌曲都有一个唯一标识符（歌曲ID），并通过艺术家ID外键与艺术家关联。

   • 歌曲ID（主键）
   • 标题
   • 长度
   • 添加日期
   • 艺术家ID（外键，关联艺术家表）
   • 专辑ID（外键，关联专辑表）
   • 曲目号
   • 发行年份
   • 流派ID（外键，关联流派表）

2. 艺术家表（Artists table）存储有关艺术家的信息，例如他们的姓名。每个艺术家有唯一的艺术家ID标识。

   • 艺术家ID（主键）
   • 姓名
   • 流派
   • 照片

3. 专辑表（Albums table）包含有关专辑的详细信息，包括标题、发行年份以及创建专辑的艺术家。每个专辑都有唯一的专辑ID，通过艺术家ID外键与艺术家关联。

   • 专辑ID（主键）
   • 标题
   • 发行年份
   • 艺术家ID（外键，关联艺术家表）

4. 流派表（Genres table）存储不同的音乐流派，每种流派都有唯一的流派ID标识，并通过流派ID外键与歌曲关联。

   • 流派ID（主键）
   • 名称

用户信息的数据库表包括：用户的基本信息、收藏歌曲、创建播放列表、关注艺术家等行为。这样的设计使得我们可以有效地管理用户的行为和偏好，并与音乐部分的数据库相结合，为用户提供个性化的音乐体验。

数据库的主键和外键

当设计数据库时，主键（Primary Key）和外键（Foreign Key）是两个重要的概念，用于确保数据的完整性和关系的正确性。

1. 主键（Primary Key）：

   • 主键是表中用来唯一标识每一行数据的字段或字段组合。
   • 每个表只能有一个主键，并且主键的值在表中必须是唯一的，不能重复。
   • 主键字段通常被用来作为表中数据的唯一标识符，并且可以用来快速检索和索引数据。
   • 主键字段不允许为空（NULL）值，即每一行必须有一个有效的主键值。

2. 外键（Foreign Key）：

   • 外键是一个字段或字段组合，它们在一个表中创建的，但指向另一个表中的主键字段。
   • 外键用于建立表之间的关系，以确保数据的一致性和完整性。
   • 外键确保了在子表中的数据引用父表中存在的数据。
   • 外键可以允许为空值，但如果有值的话，必须引用父表中的一个有效的主键值。
   • 通过外键，可以在多个表之间建立关联，实现数据的连接和查询。

代码示例（部分）：

-- 创建用户表
CREATE TABLE Users (
    UserID INT PRIMARY KEY, -- 用户ID作为主键
    Username VARCHAR(50),
    Email VARCHAR(100),
    Password VARCHAR(50),
    SubscriptionType VARCHAR(20),
    RegistrationDate DATE,
    LastLoginDate DATE
);

-- 创建歌曲表
CREATE TABLE Songs (
    SongID INT PRIMARY KEY, -- 歌曲ID作为主键
    Title VARCHAR(100),
    Length INT,
    ReleaseDate DATE,
    ArtistID INT, -- 艺术家ID作为外键
    AlbumID INT, -- 专辑ID作为外键
    TrackNumber INT,
    ReleaseYear INT,
    GenreID INT, -- 流派ID作为外键
    FOREIGN KEY (ArtistID) REFERENCES Artists(ArtistID), -- 关联到艺术家表的外键
    FOREIGN KEY (AlbumID) REFERENCES Albums(AlbumID), -- 关联到专辑表的外键
    FOREIGN KEY (GenreID) REFERENCES Genres(GenreID) -- 关联到流派表的外键
);

-- 创建收藏表
CREATE TABLE Favorites (
    FavoriteID INT PRIMARY KEY, -- 收藏ID作为主键
    FavoriteDateTime DATETIME,
    UserID INT, -- 用户ID作为外键
    SongID INT, -- 歌曲ID作为外键
    FOREIGN KEY (UserID) REFERENCES Users(UserID), -- 关联到用户表的外键
    FOREIGN KEY (SongID) REFERENCES Songs(SongID) -- 关联到歌曲表的外键
);

更多详细答案可关注公众号查阅。