问题描述
我从一张表格开始，按日期列出了 1958 年到 1980 年的前 100 首歌曲.对于每个日期，有 100 条记录.显然，随着歌曲每周改变位置，许多将是重复的.此外，艺术家将被多次复制(想想猫王).表中有 ~ 116,000 条记录.
I began with a table listing the top 100 songs by date for the years 1958 through 1980. For each date, there are 100 records. Obviously many will be duplicates as a song changes position from week to week. Also, the artists will be duplicated (think Elvis) numerous times. There are ~ 116,000 records in the table.
这个表有以下字段
uniq,
date,
artist,
title,
position
为了消除重复项(我理解的标准化)，我修改了表格，现在看起来像这样
To eliminate duplicates (normalization as I understand it) I have modified the table so that it now looks like this
uniq,
date,
artistcode,
titlecode,
position
并且有两个新表艺术家和标题.艺术家看起来像这样
And have two new tables artists and titles. Artists looks like this
artist,
artistcode
标题看起来像这样
title,
titlecode
为了朝着正确的方向开始，我只是想重新组装(连接)这些表，以便我有一个看起来像原始表的视图，即
To get started in the right direction, I simply want to reassemble (join) these tables so that I have a view that looks like the original table, ie
uniq,
date,
artist,
title,
position
并拥有那 116000 条记录.在阅读了几本书和几个教程后，我得出的结论是，我对规范化应该做什么有误解，或者我只是朝着错误的方向前进.
and has those 116000 records. After reading a couple of books and working with several tutorials, I have come to the conclusion that I have a misconception of what normalization should do, or I am simply headed in the wrong direction.
非常感谢创建视图的 SQL 语法.
The SQL syntax to create the view would be much appreciated.
推荐答案
什么重复"?多次出现相同的值本身并没有错.你需要开始阅读一些学术教科书/关于信息建模和关系数据库的幻灯片/课程.
What "duplicates"? There is nothing wrong per se with the same value appearing multiple times. You need to begin reading some academic textbook(s)/slides/course(s) about information modeling and relational databases.
在表中或不在表中的每一行都对情况进行说明.规范化地址的那种重复"和冗余"问题有时会在同一个表的多行时出现对这种情况说同样的话.(这可能涉及也可能不涉及多次出现的子行值.)
Each row that is in or not in a table makes a statement about the situation. The sort of "duplicate" and "redundancy" problems that normalization addresses arise sometimes when multiple rows of the same table say the same thing about the situation. (Which might or might not involve subrow values appearing multiple times.)
例如:如果你有一个像这样的表格，但有一个额外的列，并且给定的艺术家/标题组合总是以相同的值出现在该列中(就像一个艺术家从来没有使用相同标题图表的多个录音而你添加了每个录音的播放时间)那么就会有问题.(... AND 录制artist/title 是时间 分钟")并且其中的值总是以相同的艺术家/标题组合出现(例如，如果您添加了录音 ID)，那么就会出现问题.(... AND 录音recordingcode 的标题是title by 艺术家artist")现在没有问题.你期待什么答案?答案是，归一化表示没有问题，而归一化不会告知您的印象.
Eg: If you had a table like this one but with an additional column and a given artist/title combination always appeared with the same value in that column (like if an artist never has multiple recordings with the same title charting and you added the playing time of each recording) then there would be a problem. ("... AND recording artist/title is time minutes long") If you had a table like this one but with an additional column and a value in it always appeared with the same artist/title combination (like if you added a recording id) then there would be a problem. ("... AND recording recordingcode is of title title by artist artist") Right now there is no problem. What do you expect as an answer? The answer is, normalization says there's no problem, and your impressions are not informed by normalization.
规范化不涉及用 id 替换值. 引入的 id 值与它们的值具有完全相同的外观模式识别/替换，这样就不会消除重复"，而是在新表中添加更多的重复"ID.作为视图的原始表是新表在 id 相等上的连接的投影.(您可能想要有 id 以便于更新或数据压缩(等)，但代价是更多的表和连接(等).这是一个单独的问题.)
Normalization does not involve replacing values by ids. Introduced id values have exactly the same pattern of appearances as the values they identify/replaced, so that doesn't "eliminate duplicates", and it adds more "duplicates" of the ids in new tables. The original table as a view is a projection of a join of the new tables on equality of ids. (You might want to have ids for ease of update or data compression (etc) at the expense of more tables & joins (etc). That's a separate issue.)
-- hit `uniq` is title `title` by artist `artist` at position `position` on date `date`
/* FORSOME h.*, a.*, t.*,
hit h.uniq is title with id h.titlecode by artist with id h.artistcode
at position h.position on date h.date
AND artist a.artist has id a.artistcode AND h.artistcode = a.artistcode
AND title t.title has id t.titlecode AND h.titlecode = a.title
AND `uniq` = h.uniq AND `title` = t.title AND `artist` = a.artist
AND `position` = h.position AND `date` = h.date
*/
/* FORSOME h.*, a.*, t.*,
Hit(h.uniq, h.titlecode, h.artistcode, h.position, h.date)
AND Artist(a.artist, a.artistcode) AND h.artistcode = a.artistcode
AND Title(t.title, t.titlecode) AND h.titlecode = a.title
AND `uniq` = h.uniq AND `title` = t.title AND `artist` = a.artist
AND `position` = h.position AND `date` = h.date
*/
create view HitOriginal as
select h.uniq, h.date, a.artist, t.title, h.position
from Hit h
join Artist a on h.artistcode = a.artistcode
join Title t on h.titlecode = t.titlecode
这篇关于理解规范化重复 - 我猜我没有 - 添加艺术家和标题 ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，WP2