用于音乐图表跟踪器的SQLite数据库设计

我已经建立了一个小的SQLite数据库来跟踪iTunes RSS提要中的前100首歌曲。我已经在Bash中构建了这个脚本来完成所有的艰苦工作，它终于开始工作了，但我不确定我的数据库结构是否正确，所以我正在寻找一些关于最佳方法的反馈，因为我现在只在学习SQL，所以我不想在构建查询以及时检索数据时陷入困境！

我有三张这样的桌子；

艺人表

artist_id - PK
artist_name

歌曲表

song_id - PK
artist_id - FK (from the artists table)

图表表

chart_id - PK
song_id - FK (from the songs table)
position - (chart position 1-100)
date - (date of chart position xxxx-xx-xx)

艺术家和歌曲表对我来说似乎很好，得到了国外的关键约束。。。等等，但我不确定图表，这个结构有什么明显的问题吗？

我想随着时间的推移跟踪歌曲/艺术家/位置，这样我就可以生成一些统计数据。。。etc

谢谢，

初始响应

为了回答你的问题，我问了你关于数据的问题，但你一直在告诉我这个过程。毫无疑问，这对你来说非常重要。现在您希望确保记录归档系统是正确的。

就我个人而言，在设计好数据库之前，我从不写一行代码。部分原因是我讨厌重写代码(而且我喜欢编写代码)。你的顺序颠倒了，这是最近一个不幸的趋势。这意味着，无论我给你什么，你都必须重写大块的代码。

(b.1)如何准确检查艺术家[歌曲]是否已经存在

(b.2)你怎么知道档案中没有超过occ的特定艺术家/歌曲

现在，考虑到你的问题中的细节，假设你有消息，Pussycat Dolls在今天的MTV排行榜上排名第66位：

INSERT artist VALUES ( "Pussycat Dolls" )    -- succeeds, intended
INSERT artist VALUES ( "Pussycat Dolls" )    -- succeeds, unintended
INSERT artist VALUES ( "Pussycat Dolls" )    -- succeeds, unintended

今天究竟是哪个Pussycat Dolls记录排在第66位？当RFS增长，并且artist中有更多字段时，例如birth_date,，您希望更新三条记录中的哪一条？
宋也是如此。
Chart是如何识别的，它是否类似于US Top 40？

(b.1)如何准确检查艺术家[歌曲]是否已经存在

执行代码时，它会在sqLite程序中运行。您传递的确切SQL字符串是什么？假设你这样做：

SELECT $artist_id = artist_id
FROM artist
WHERE artist_name = $artist_name
IF $artist_id = NULL
INSERT artist VALUES ( $artist_name )

然后，当系统"上线"时，你会有一些惊喜。希望这种互动能消除他们。现在你有几百个艺术家。

当你有几千个艺术家时，系统会慢到蜗牛般的速度。
当事情出了问题，你会有重复的艺术家，歌曲，排行榜。

记录归档系统

现在，您有一个1970年代以前的ISAM记录归档系统，没有关系完整性、功能或速度。

如果您想在当今的关系环境中了解更多关于RFS的危险，请阅读本答案。

关系数据库

据我所知，您需要关系数据库的完整性、功能和速度。这是你要去的地方。显然，它是不完整的，未经证实的，可能有细节缺失，许多问题仍然悬而未决。但我们必须对数据进行建模，只将其作为数据(而不是你要用它做什么，过程)，而不是数据。

这种方法将确保许多方面：

随着数据的增长和添加(就结构而言，而不是总体而言)，现有的数据和代码不会改变
您将拥有数据和引用完整性
您可以通过一个SELECT命令获得每个统计信息。
您可以对数据执行任何SELECT，甚至是您无法想象的SELECT，这意味着无限制的统计数据。只要数据以关系形式存储即可。

数据库是关于现实世界的事实的集合，仅限于关注的主题领域。到目前为止，我们还没有事实，我们有一个传入RSS流的记录。录音没有完整性，你的代码也没有什么可依赖的。这正朝着事实的方向发展：

初稿音乐图表TRD(由于进展而过时，见下文。)

对意见的回应1

目前，我只跟踪一个图表，但我在您的模型中看到，它也有能力跟踪几个图表，这很好

不是。这是正确做事的副作用。此处的问题是标识之一。图表位置不是由RSS Feed ID或chart_table.id,加PositionNo加DateTime.号标识的。图表位置被标识为US Top 100/27 Apr 15/1…。副作用是ChartName是标识符的一部分，它允许多个图表，而无需额外编码。

在IT黑暗的日子里，人们经常为一个国家编写系统，并在各地实施StateCode。然后，当他们向国际客户群开放时，会遇到巨大的问题。问题是，不存在没有国家的国家，一个国家只存在于一个国家的背景下。因此，国家的标识符必须包括一个国家标识符，它是(CountryCode, StateCode).澳大利亚和加拿大都有NT作为StateCode.

如果我能解释如何存储rss提要中的数据，它可能会在一定程度上澄清问题

请不要。这是关于数据的，而且只是数据。请回顾我以前对这个问题的评论，以及好处。

我现在不在我的主计算机上，但如果可以的话，我会在接下来的几个小时内做出回应。

不用担心。我明天就去。

不过，你的模型对我来说确实有意义，

这是因为你非常了解数据值，但你不了解数据，当有人正确地为你列出数据时，你会体验到令人愉快的识别感。

我不介意重新编码所有内容，这是一条学习曲线

这是因为你本末倒置，根据电子表格中的数据进行编码，而不是先设计数据库，然后再根据第二个数据进行编码。

如果你不习惯这种符号，请注意，每一个小记号、凹口和标记，实线与虚线，直角与圆角，都意味着非常具体的东西。请参阅IDEF1X符号。

对意见2的回应

还有一个快速问题

开火，直到你完全满意为止。

在图表中，将艺术家表放在歌曲表之上，并将歌曲表作为父艺术家的子表，会有什么缺点吗？艺术家可以有很多首歌，但每首歌只能有一个艺术家。是否需要额外的表格只包含艺人PK和歌曲PK。我可以不将艺人PK作为FK存储到歌曲表中吗？因为只有在有相关艺人的情况下，歌曲才能存在

注意你对组织方式的依恋。我重复一遍：

数据库是关于现实世界的事实的集合，仅限于关注的主题领域

事实是合乎逻辑的，而不是物理的。当这些事实被正确组织时(规范化、设计)：

您可以对数据执行任何SELECT，甚至是您无法想象的SELECT，这意味着无限制的统计数据。只要数据以关系形式存储即可。

如果它们不是，你就不能。针对数据的所有SQL(不仅仅是设想的报告)都受模型中的限制，这归结为一件事：离散事实是否以逻辑形式记录。

通过TRD，我们已经发展到记录真实世界的事实，仅受应用程序范围的限制，而不受事实的非自由裁量权的限制。

我是否可以不将artistPK作为FK存储到歌曲表中，因为只有在有关联的艺术家的情况下，歌曲才能存在

在您的工作环境中，此时此刻，这是真的。但在你录制的真实世界中，情况并非如此。如果应用程序或您的作用域发生更改，您将不得不更改数据库和应用程序的大块。如果您正确记录了事实，因为它们存在，而不限于您当前的应用程序范围，那么当应用程序或您的范围发生更改时，就不需要进行此类更改(当然，您必须添加对象和代码，但不能修改现有的对象和代码)。

在现实世界中，Song和Artist是离散的事实，它们可以相互独立地存在。你的提议是错误的。

Ave Maria在Karen Carpenter记录之前已经存在了16个世纪。
而且你已经理解并接受了一个Artist没有"歌"的存在。

是否需要额外的表格只包含艺人PK和歌曲PK

它不是一个"只包含艺人PK和歌曲PK的附加表"，它记录了一个离散的事实(独立于Artist和Song的独立存在)，即特定的Artist记录了特定的Song. That is the fact that you will count on in the图表日期位置`

你的命题认为Song依赖于Artist，而这根本不是真的。任何和所有基于Song的统计数据(梦想与否)都必须导航Artist::ArtistSong，然后排序或ORDER BY,等。

艺术家可以有很多首歌曲，但每首歌曲只能有一位艺术家

这是正确的一半(在您当前的工作环境中是正确的，但在现实世界中不是正确的)。事实是：

Each Artist is independent
Each Song is independent
Each Artist recorded 1-to-n Songs (via ArtistSong)
Each Song was recorded by 1-to-n Artists (via ArtistSong)

为了理解，改变上面的单词以形成正确的命题(而不是陈述技术上正确的谓词)：

Artists can have many RecordedSongs
Each RecordedSong can only have 1 ArtistEach RecordedSong can only have 1 Song

所以是的，有缺点，有重大缺点。

这就是为什么我说，你必须脱离应用程序、使用，并将数据建模为数据，而不是数据。

解决方案2

我已经更新了TRD。

第二稿音乐图表TRD

Courier表示示例数据；蓝色表示Key(Primary总是第一个)；管道表示柱分离；斜线表示备用密钥(只显示不在PK中的列)；绿色表示非按键。
我现在给你谓词。这些都非常重要，原因有很多。这里的主要原因是它消除了我们正在讨论的问题的歧义。
- 如果您想了解有关谓词的更多信息，请访问this Answer，向下滚动(向下滚动！)至Predicate并阅读该部分。还评估那个TRD和那些谓词
ChartDateSong上的索引需要解释。起初我认为：
```
PK ( Chart, Date, Rank )
```
但出于完整性和搜索的目的，我们需要：
```
AK ( Chart, Date, ArtistId, SongId )
```
这是一个更好的PK。所以我换了它们。我们确实需要两者。(我不知道NONsqLite，如果它有聚集索引，则应该聚集AK,而不是PK。)
```
PK ( Chart, Date, ArtistId, SongId ) 
AK ( Chart, Date, Rank )
```

对评论3的回应

如果一首歌曲以与歌曲表中的唱片相同的歌曲名称进入排行榜，但完全无关(不是封面，完全原创，只是碰巧同名)，情况会怎样？

在文明国家，这被称为欺诈，通过欺骗获得利益，但我会试着用魔鬼的语言思考一下，然后回答这个问题。

好吧，如果真的发生了，那么你必须迎合它。提要是如何通知你这样的事件的？我相信不会。那么你的歌曲标识符仍然是名字。

，而不是创建一个唯一的歌曲记录，而是将现有的song_id添加到具有艺术家id的artistssons_table中，这不是一个问题吗

我们不知道更好的，所以这不是问题。看那条新闻的人也没有比这更清楚的了。如果你通过任何渠道收到通知你该问题的数据，并且你可以指定它，你可以更改它

通常，我们有一个应用程序，可以导航层次结构并更改它们，例如，ReferenceMaintenance应用程序，左侧有一个Exporer类型的窗口，右侧有组合对话框(顶部有occ列表，底部有一个occ的详细信息)。

在那之前，它不是一种腐败形式，因为防止这种腐败的约束是未定义的。你不能因为违反了尚未成文的法律而被判有罪。除了流氓国家。

虽然一首歌可以有相同的名字，但这并不一定意味着它是同一张唱片

是。

区分艺术家的歌曲不是更好吗

它们按艺术家进行区分。

你很欣赏一首歌和一个艺术家演奏一首歌是两个离散的事实，是吗？请质疑任何不意味着完全意义的谓词，这些谓词是数据库支持的命题。

Ave Maria作为一个独立的事实存在于Song中
Karen Carpenter, Celine Dion, and Yours Truly作为三个独立的事实存在，在Artist中
CCD_ 42在CCD_
这是七个独立的事实，关于一个Song,关于三个Artists.

对评论的回应4

我现在明白了。artistsong_table是两个项目"相遇"的地方，一种关系实际上存在并且是唯一的

是。我只是不想那样说。术语Fact在技术上有着精确的含义，超越了英语的含义。

数据库是关于现实世界的事实的集合，仅限于关注的主题领域

也许再读一次我的回复3，记住对事实的理解。

每个ArtistSong行都是一个事实。这取决于一个艺术家的事实和一首歌的事实。它确立了艺术家录制这首歌的事实。ArtistSong事实是层次结构中较低的其他事实所依赖的事实。
"关系…实际上"。我想你的意思是"实例"。表之间存在关系，因为我画了一条线，您将实现外键约束。也许可以把事实看作一个"实例"。

为了确保我正确理解这个想法，如果我在组合中添加"流派"，我认为会创建一个新的"独立"表Genre_table，而artistsong_table将继承其PK作为FK，这是正确的吗

是。这是一个经典的引用或查找表，关系将是不可识别的。我对音乐妓院的了解还不足以发表任何声明，但据我所知，流派适用于一首歌；艺术家；和一首ArtistSong(他们可以用与歌曲不同的流派演奏歌曲。流派)。你给了我一首，所以我会为它建模。

这样做的结果是，当您在ArtistSong中插入行时，您必须具有流派。如果这在提要中，那么很好，如果没有，那么你就有一个处理问题要处理。克服这个问题的简单方法是，实现一个流派"，这向你表明你需要从其他渠道来确定它。

稍后添加一个分类器(例如Genre)很容易，因为它是一个非标识关系。但识别项目以后很难添加，因为它们会迫使密钥发生更改。请参阅我的答复1第3段。

您可能已经准备好使用数据模型：

第三稿音乐图表数据模型

这一切都取决于数据将具有的关系(一对一、一对多、多对多)。

您实现图表表的方式表明：

每个排行榜只有/属于一首歌
一首歌可以有很多排行榜

这是一种一对多的关系。如果这是你想要的，那么一切似乎都很好。

但是：

如果你的排行榜可以有很多首歌，而一首歌只有一首chart(也是一对多关系，但相反)，song_id列需要从图表表和歌曲表中取出中的chart_id列
如果你的排行榜可以有很多歌曲，你的歌曲也可以有很多排行榜(多对多关系)，那么你需要一个"联合表"，它可以是这样的：表：charts_ssongs，列：id，chart_id，song_id，位置