如何从此数据库布局的1：M关系中删除冗余，仍然能够摘录所需的数据？

本文介绍了如何从此数据库布局的1：M关系中删除冗余，仍然能够摘录所需的数据？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

数据库如下所示：

  CREATE TABLE artists（
 artist_id SERIAL PRIMARY KEY，
 artist TEXT UNIQUE NOT NULL 
）; 
 
 CREATE TABLE artistalias（
 artistalias_id SERIAL PRIMARY KEY，
 artist_id SERIAL REFERENCES artists（artist_id），
 alias TEXT UNIQUE NOT NULL 
）; 
 
 CREATE TABLE歌曲（
 song_id SERIAL PRIMARY KEY，
 song TEXT NOT NULL，
 artist_id SERIAL REFERENCES artists（artist_id）
）;

 
 一位艺术家可以有零个，一个或多个别名
 
 一个别名只属于一个艺术家
 
 一首歌曲有一个艺术家
 
 一个艺术家可以有一首或多首歌曲

 
 
 我的问题是数据库保存使用两个或多个假名的艺术家。例如，一位艺术家对属于某个流派的歌曲使用舞台名称刺客，对属于另一个歌曲的歌曲使用代理Sasco 。 
 
 
 后来网站应该显示以下格式的数据：
 艺术家| Song 
 ------------ + ------------------- 
 Assassin |任何地方我们走
 Agent Sasco |我们观看
  
当你点击艺术家，它会链接到一个页面显示所有不同aliases艺术家曾经用来表演歌曲。 
重要的是，歌曲显示的艺术家的假名，它是与释放。
 
 
 我使用的虚拟数据看起来像： / p> 
 
 
  INSERT INTO artists（artist）VALUES 
（'Assassin'），（'Agent Sasco'）， '）; 
 
  - 这让我作为它的很多冗余数据
 INSERT INTO artistalias（artist_id，别名）VALUES 
（1，'Agent Sasco'），（2，刺客'）; 
 
 INSERT INTO歌曲（song，artist_id）VALUES 
（'Everywhere We Go'，1），（'We Dem A Watch'，2），（'Only Takes Love'，3 ）; 
  
这个数据库布局让我烦恼的是，我必须添加冗余数据到 artistalias 。必须有更好的方法将表格艺术家链接到 artistalias 和歌曲 
 
 
以所需格式显示数据的查询如下：
  SELECT 
 artist AS pseudonyme_song_was_performed_with，
 string_agg（alias，'&'）AS other_pseudonymes，
 song 
 FROM 
艺术家
 left JOIN artistalias USING（artist_id）
 left JOIN歌曲USING（artist_id）
 GROUP BY艺术家，歌曲; 
  
这里是。布局和数据如上所述。
解决方案
将艺术家的真实姓名存储在 artists 表中。
 
 
 然后将该艺术家的所有别名存储在 artistalias 表。 
 
 
然后将artistalias_id存储在歌曲表。
 
 
 这样，您就不会有任何重复的数据。
  CREATE TABLE artists（
 artist_id SERIAL PRIMARY KEY，
 artist TEXT UNIQUE NOT NULL 
）; 
 
 CREATE TABLE artistalias（
 artistalias_id SERIAL PRIMARY KEY，
 artist_id SERIAL REFERENCES artists（artist_id），
 alias TEXT UNIQUE NOT NULL 
）; 
 
 CREATE TABLE歌曲（
 song_id SERIAL PRIMARY KEY，
 song TEXT NOT NULL，
 artistalias_id SERIAL REFERENCES artistalias（artistalias_id）
）; 
  
然后以这种方式插入数据：
  INSERT INTO艺术家（艺术家）VALUES 
（'Jeffrey Campbell'），（'Sizzla'） 
 
  - 这个冗余是我的身份
 INSERT INTO artistalias（artist_id，别名）VALUES 
（1，'Agent Sasco'），（1，'Assassin'）; 
 
 INSERT INTO歌曲（song，artistalias_id）VALUES 
（'Everywhere We Go'，1），（'We Dem A Watch'，2）; 
  
并以这种方式查询：
  SELECT 
 a1.alias AS pseudonyme_song_was_performed_with，
 string_agg（a2.alias，'&'）As other_pseudonymes，
 song 
 FROM 
 artistalias a1 
 left JOIN artistalias a2 on a2.artist_id = a1.artist_id 
 left JOIN歌曲在s.artistalias_id = a1.artistalias_id 
 GROUP BY a1.alias，song; 
  
小提琴： 
 
The database looks like this:
CREATE TABLE artists (
    artist_id       SERIAL  PRIMARY KEY,
    artist          TEXT    UNIQUE NOT NULL
);

CREATE TABLE artistalias (
    artistalias_id  SERIAL  PRIMARY KEY,
    artist_id       SERIAL  REFERENCES artists (artist_id),
    alias           TEXT    UNIQUE NOT NULL
);

CREATE TABLE songs (
    song_id         SERIAL  PRIMARY KEY,
    song            TEXT    NOT NULL,
    artist_id       SERIAL  REFERENCES artists (artist_id)
);
one artist can have zero, one or many aliases
one alias belongs to exactly one artist
one song has one artist
one artist can have one or many songs
My problem is that the database holds artists that are using two or more pseudonymes. For example one artist is using the stage name Assassin for songs that belong to one certain genre and the name Agent Sasco for songs that belong to another.Some artists just randomly change their pseudonymes every now and then.
Later a website should display the data in the following format:
Artist      | Song
------------+-------------------
Assassin    | Anywhere We Go
Agent Sasco | We Dem A Watch
And when you click on the artist it will link you to a page showing all different aliases the artist has used to perform songs with.It is important that the song is being displayed with the pseudonyme of the artist that it was released with.
The dummy data I work with looks like:
INSERT INTO artists (artist) VALUES
  ('Assassin'), ('Agent Sasco'), ('Sizzla');

-- This bothers me as its a lot of redundant data
INSERT INTO artistalias (artist_id, alias) VALUES
  (1, 'Agent Sasco'), (2, 'Assassin');

INSERT INTO songs (song, artist_id) VALUES
  ('Anywhere We Go', 1), ('We Dem A Watch', 2), ('Only Takes Love', 3);
What bothers me with this database layout is that I have to add redundant data to artistalias. There must be a better way to link the table artists to artistalias and songs without having to add one specific artist and his aliases multiple times?
The query to display the data in the desired format looks like:
SELECT
  artist AS pseudonyme_song_was_performed_with,
  string_agg(alias, ' & ') AS other_pseudonymes,
  song
FROM
  artists
  left JOIN artistalias USING (artist_id)
  left JOIN songs USING (artist_id)
GROUP BY artist, song;
Here is a SQLFiddle with the layout and data as described above.
 解决方案 
You should store the real name of the artist in the artists table.
Then store all the aliases for that artist in the artistalias table.
Then store the artistalias_id in the songs table.
In that way, you won't have any duplicate data.
    CREATE TABLE artists (
        artist_id       SERIAL  PRIMARY KEY,
        artist          TEXT    UNIQUE NOT NULL
    );

    CREATE TABLE artistalias (
        artistalias_id  SERIAL  PRIMARY KEY,
        artist_id       SERIAL  REFERENCES artists (artist_id),
        alias           TEXT    UNIQUE NOT NULL
    );

    CREATE TABLE songs (
        song_id         SERIAL  PRIMARY KEY,
        song            TEXT    NOT NULL,
        artistalias_id  SERIAL  REFERENCES artistalias (artistalias_id)
    );
Then insert the data this way:
    INSERT INTO artists (artist) VALUES
      ('Jeffrey Campbell'), ('Sizzla');

    -- THIS REDUNDANCY IS BOTHERING ME
    INSERT INTO artistalias (artist_id, alias) VALUES
      (1, 'Agent Sasco'), (1, 'Assassin');

    INSERT INTO songs (song, artistalias_id) VALUES
      ('Anywhere We Go', 1), ('We Dem A Watch', 2);
And query this way:
    SELECT
      a1.alias AS pseudonyme_song_was_performed_with,
      string_agg(a2.alias, ' & ') AS other_pseudonymes,
      song
    FROM
      artistalias a1
      left JOIN artistalias a2 on a2.artist_id = a1.artist_id
      left JOIN songs s on s.artistalias_id = a1.artistalias_id
    GROUP BY a1.alias, song;
Fiddle: http://sqlfiddle.com/#!15/3a78c/8/0
                        这篇关于如何从此数据库布局的1：M关系中删除冗余，仍然能够摘录所需的数据？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！