音乐库 MySQL 数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9234182/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Music library MySQL database
提问by Throdne
I need some help building a table(s) for a music database in MySQL. I am unsure on how to lay this information out in a table.
我需要帮助在 MySQL 中为音乐数据库构建一个表。我不确定如何将这些信息放在表格中。
Here is the info I would like to be stored in the database.
这是我想存储在数据库中的信息。
Artist Name
Album Name
Release Date
Genre
Picture URL (album artwork)
Track Number
Trank Name
Track Playtime
Lyric (optional, But would like to have it someday)
etc.
Basically anything that has to do with organizing digital music. I'm new to databases and I have some ideas. But if I'm going to learn I might as well learn the right way of doing it.
基本上任何与组织数字音乐有关的事情。我是数据库的新手，我有一些想法。但如果我要学习，我不妨学习正确的做法。
Any thoughts on how to design my table(s) would be awesome.
关于如何设计我的桌子的任何想法都会很棒。
回答by Michael Berkowski
Something like this would be good to start with. It specifies a table for artists, albums (with keys into artists and genres), tracks (keyed into albums), and genres.
像这样的事情开始会很好。它为艺术家、专辑（带有艺术家和流派的键）、曲目（键入专辑）和流派指定了一个表。
Table artists
----
id (primary key),
name
description
years_active
otherinfo (whatever you need)
Table albums
----
id (primary key)
artistid (foreign key to artists table)
name,
releasedate
genreid (foreign key to genres table)
picture
Table tracks
----
id (primary key)
albumid (foreign key to albums table)
name
override_artist (overrides album artist if not null)
playtime
lyric
otherstuff as needed
Table genres
----
id (primary key)
name
description
回答by Stelian Matei
I suggest the following database structure:
我建议使用以下数据库结构：
artist { id, name }
genre { id, name }
album { id, name, artist_id, release_date, genre_id, picture_url }
track { id, album_id, number, name, playtime, lyrics }
回答by judda
Artist ( ArtistID INT PRIMARY KEY, ArtistName )
Genre ( GenreID TINYINT PRIMARY KEY, GenreDescription )
Album ( AlbumID INT PRIMARY KEY, ArtistID INT, GenreID INT ReleaseDate )
AlbumArt ( AlbumArtID INT PRIMARY KEY, AlbumID INT, AlbumArtPath )
Track ( AlbumID INT, TrackNumber INT, TrackName, PlayTime, Lyrics , PRIMARY KEY ( AlbumID, TrackNumber ) )
回答by iluwatar
The database schematic is about laying out the tables and establishing the relationships between those tables.
数据库原理图是关于布置表并建立这些表之间的关系。
You have already identified many table candidates: Track, Artist, Album
您已经确定了许多候选表：Track、Artist、Album
Then you need the relationships: Artist can have many albums, album can have many tracks.
然后你需要关系：艺术家可以有很多专辑，专辑可以有很多曲目。
With these few simple tables and relationships in place you already have a small database.
有了这几个简单的表和关系，您就已经拥有了一个小型数据库。
回答by Adrian J. Moreno
Check out http://www.freedb.org/en/. Formerly cddb.org, it's a free music database. You can download the whole database from one of their links and see how they've done it.
查看http://www.freedb.org/en/。以前的 cddb.org，它是一个免费的音乐数据库。您可以从他们的链接之一下载整个数据库，看看他们是如何做到的。
