Início / dba / Perguntas / 344457
Accepted
sterroso
Asked: 2024-12-31 03:29:27 +0800 CST

PostgreSQL relacionamento um para muitos transpondo segunda tabela

  • 772

Tenho duas tabelas, para simplificar vamos chamá-las employeesde e employee_comms:

employee:

eu ia nome departamento
1 Jane Manutenção
2 João Projeto

employee_comms:

eu ia ID do contato tipo_de_comunicação valor_de_comunicação
1 1 e-mail [email protegido]
2 1 telefone 555 324 6573
3 2 e-mail [email protegido]
4 2 telefone 555 201 5843

Atualmente, comms_typesó pode ser phoneou email.

Escrevi uma consulta para filtrar funcionários por departamento e mostrar e-mail e telefone de cada um:

SELECT
  d.id,
  d.name,
  d.dept,
  p.comms_value as phone,
  e.comms_value as email
FROM
  (
    SELECT *
    FROM employee
    WHERE dept = 'Design'
  ) AS d
  LEFT JOIN (
    SELECT contact_id, comms_value
    FROM employee_comms
    WHERE comms_type = 'phone'
  ) AS p ON (d.id = p.contact_id)
  LEFT JOIN (
    SELECT contact_id, comms_value
    FROM employee_comms
    WHERE comms_type = 'email'
  ) AS e ON (d.id = e.contact_id);

O resultado é uma tabela onde a segunda tabela é transposta com linhas únicas e exclusivas onde nenhum employeevalor é duplicado ( id, name):

eu ia nome departamento telefone e-mail
2 João Projeto [email protegido] 555 201 5843

Atualmente, ele resolve minhas necessidades, mas continuo pensando que deveria haver uma solução mais eficiente, já que estou consultando a employee_commstabela duas vezes e isso pode se tornar um problema de desempenho quando a employee_commstabela ficar grande (e isso vai acontecer).

P: Existe uma abordagem mais eficiente?

postgresql

1 respostas

  1. Best Answer

    Você poderia fazer algo assim (todo o código abaixo está disponível no fiddle aqui ):

    SELECT
  e.id, e.name, e.dept,
  STRING_AGG(ec.comm_type, ', '  ORDER BY ec.e_id, ec.comm_type) AS ,
  STRING_AGG(ec.comm_value, ', ' ORDER BY ec.e_id, ec.comm_type)
FROM
  employee_comm ec
JOIN employee e ON ec.e_id = e.id
WHERE ec.comm_type IN ('phone', 'email') AND e.dept = 'Design'
GROUP BY e.id, e.name, e.dept
ORDER BY e.id;

    Resultado (adicionei alguns outros dados aos seus):

    id name  dept        Ct Types       Ct values
 2  Joe   Design      email, phone   [email protected], 555 201 5843

    Confira os ANALYZE (EXPLAIN...trechos que incluí nos meus violinos.

    Há uma série de problemas com seu esquema.

    Você está usando o ( temido ) modelo EAV (Entity Attribute Value) - isso é (geralmente - > 99,99...%) um bad idea™! Veja aqui o que tem a dizer sobre isso - também aqui (e links dentro). Você está misturando números de telefone e e-mails no mesmo campo - em vez de misturá-los na mesma tabela - o que é bom.

    No entanto, você tem mais de um tipo de comunicação por pessoa - então o que você precisa é de uma associative entity(também conhecida como tabela de junção (há > 15 outros nomes na página Wiki).

    OU você pode ter uma tabela separada para cada tipo de comunicação - ou seja, para telefone(s), e-mail(s)... essa também é uma abordagem válida - se não houver centenas desses tipos.

    Então, pegando a segunda ideia primeiro, criei uma emailtabela (e uma phonetabela - não mostrada, veja o violino aqui ) como segue:

    CREATE TABLE email
(
  
  e_id      SMALLINT NOT NULL,
  e_value   TEXT     NOT NULL,

  CONSTRAINT email_pk
    PRIMARY KEY (e_id, e_value),  -- nobody can have the same email twice
  
  CONSTRAINT email_val_ck 
    CHECK (e_value ~ '^[a-zA-Z0-9]{1,8}@[a-zA-Z0-9-]{2,8}\.[a-zA-Z0-9]{3}') -- simple check
  
);

    Agora, observe que eu posso implementar CHECK CONSTRAINTs - estes são um good thing™! Seu banco de dados é seu último bastião de defesa contra dados ruins, então usar uma regex para verificar novamente se você não tem números de telefone ou e-mails inválidos deve (pelo menos ajudar a) manter seus dados válidos!

    Então, você terá consultas como esta:

    SELECT 
  e.id,
  e.name, e.dept, 
  COALESCE(em.e_value, 'NO EMAIL') AS email, 
  COALESCE(ph.p_value, 'NO PHONE') AS phone
FROM
  employee e
LEFT JOIN email em ON e.id = em.e_id
LEFT JOIN phone ph ON e.id = ph.e_id
ORDER BY e.name, ph.p_value;

    Resultado:

    id name  dept         email             phone
1  Jane  Maintenance  [email protected]  555 324 6578
2  Joe   Design       [email protected]  555 201 5843
3  No E  No Email     NO EMAIL          000 000 0000
4  No P  No phone     [email protected]   NO PHONE

    Você pode adicionar mais tabelas conforme aqui - adicionei um prioritycampo se você quiser usar a 1ª prioridade do usuário como método de contato preferencial.

    Você tem então:

    SELECT
  ec.e_id AS "Emp ID",
  STRING_AGG(cm_name,  ', ' ORDER BY ec.e_id, ec.m_priority) AS "Order",
  STRING_AGG(ec.m_val, ', ' ORDER BY ec.e_id, ec.m_priority) AS "Comm method",
  e.e_comment AS "Comment"
FROM 
  emp_comm ec
JOIN comm_method cm ON ec.m_id = cm.id
JOIN employee e     ON ec.e_id = e.id
WHERE cm.cm_name IN ('Email', 'Phone')  -- ET not here because he has neither E nor P!
GROUP BY ec.e_id, e.e_comment  
ORDER BY ec.e_id;

    Resultado:

    Emp ID Order         Comm method                    Comment
1      Email, Phone  [email protected], 555 324 6573 Email and Phone
2      Phone, Email  555 201 5843, [email protected] Email and Phone
3      Phone         000 000 0000                   Phone, no Email
4      Email         [email protected]                Email, no Phone

    Basicamente, você pode obter muito mais informações muito mais rápida e facilmente ao não usar o paradigma EAV ( antipadrão ) e seus stakeholders/clientes também ficarão mais felizes! Um EAV tem complexidade ON^2 (ou ON!) - conforme você adiciona mais registros às suas tabelas EAV, suas consultas rapidamente se tornarão monstros incontroláveis ​​- enquanto que se você distinguir claramente suas entidades, sua vida e suas consultas serão muito mais fáceis!

    • 1

relate perguntas