Design: Qual deve ser meu PK, FK etc.

Cada Entidade (ou Tabela) "principal" representa instâncias (ou seja, Registros) de um objeto que pode existir por si só , na ausência de qualquer outra Entidade, por exemplo, uma Pessoa. O atributo que identifica exclusivamente cada instância é um candidato à chave primária nessa tabela. A ideia de uma chave primária é que você determine (ou aloque) seu valor quando um registro é criado pela primeira vez e esse valor permanece inalterado durante todo o tempo de vida desse registro até que ele seja final e permanentemente destruído .

Por exemplo, uma Pessoa pode ser identificada exclusivamente pelo seu Número de Segurança Social, mas pode haver circunstâncias muito excepcionais em que mesmo esse número tenha de ser alterado. Nesses casos, um identificador numérico arbitrário servirá como chave primária (mas ainda com uma restrição exclusiva no SSN). YMMV.

No seu diagrama, eu diria que suas chaves primárias e chaves estrangeiras deveriam ser:

"Set MetaData" 
   PK:(Set)

"Measurement MetaData" 
   PK:(Set, Kopf)
   FK:(Set) -> "Set MetaData" (Set)

"Measurement Data"
   PK:(Set, Kopf, Time)
   FK:(Set, Kopf) -> "Measurement MetaData" (Set, Kopf)

Agora, algumas pessoas podem ficar horrorizadas com essas chaves primárias compostas e naturais, mas, desde que você as tenha escolhido corretamente, elas não serão um problema.

No PowerBI, isso resultou em um relacionamento 1:1 entre as Tabelas 1 e 2, que deveria ser uma relação um para muitos (?). Além disso, não houve nenhuma relação entre as tabelas 2 e 3.

Eu sugeriria que seu conjunto de ferramentas está decepcionando você, não sua modelagem de dados.

Se você é novo no SQL:

(set, kopf, time) é uma tupla.

Dizer (set, kopf, time) é único significa que pode haver apenas uma linha com os valores específicos (set, kopf, time). Isso não significa que nenhuma das colunas seja única, apenas a combinação específica de valores específicos nas colunas é única. Isso também significa que para cada valor específico de (set, kopf), pode haver apenas uma linha por valor de (tempo).

• Tabela de medição.

Se (set, kopf, time) for único, então pode ser a chave primária. Como uma chave primária cria automaticamente um índice em (set, kopf, time), isso também oferece uma maneira rápida de SELECT WHERE set=... AND kopf=... ORDER BY time.

Se os dados fornecerem naturalmente uma chave primária, você não precisará criar um ID.

Neste caso, porém, há uma dúvida porque "set" é varchar. Se forem apenas alguns caracteres, tudo bem. No entanto, se for uma sequência grande e você tiver muitas linhas, poderá descobrir que uma proporção significativa do espaço em disco usado pela sua tabela consiste nessa sequência. Neste caso, pode ser útil adicionar um id à tabela "Metadados de medição", remover set e kopf de "Medição" e usar esse id.

Outra situação em que você pode ter problemas é se houve um erro ao digitar os valores da string usados ​​​​em "set" e "kopf", ou qualquer situação que exija atualizações. Para atualizar esses valores, se eles forem usados ​​como chaves estrangeiras, você deverá atualizá-los em todos os lugares. Isso pode ser feito automaticamente via ON UPDATE CASCADE, mas se houver muitas linhas pode demorar um pouco. Além disso, ele não atualizará coisas fora do banco de dados, como nomes de arquivos de gráficos que foram gerados usando os dados ou legendas nos gráficos que dizem "número do conjunto de dados, qualquer que seja".

O ponto dos IDs gerados é que não há nada de especial neles, eles não significam nada além de se referir a uma linha específica, portanto, nunca precisam ser atualizados e nunca são reutilizados. Assim, coisas fora do banco de dados que possam se referir a linhas específicas usando o id não quebrarão a referência se o título, nome ou outros campos de texto forem atualizados. Estou usando gráficos como exemplo, mas você entendeu, pode ser qualquer coisa. Se você usar algo como chave primária, nunca precisará ser atualizado.

Devo adicionar IDs de série com incremento automático a essas tabelas? Intuitivamente faz sentido para as tabelas 1 e 2

Para set_metadata, eu diria que sim.

Para medição_metadata, não tenho certeza. Você também pode:

• Use (set_id,kopf) como PK, então o mesmo que FK em Measurement_data

• Adicione um ID PK gerado automaticamente a Measurement_Metadata que identifica a linha com específico (set_id,kopf) e use esse ID em Measurement_Data.

não tanto para a tabela 3 quanto para 360 pontos de dados de um único teste, não precisa realmente de um ID incrementado?

sim: presumivelmente você não tem duas medidas ao mesmo tempo para o mesmo (set, kopf), então não precisa de um ID extra para distingui-las.

como eu adicionaria o "set_id" na tabela com o mesmo valor do "set_id" referenciado da tabela 1. Afinal, eu precisaria de até 6 registros com o mesmo "set_id".

Suponha que "set_metadata" tenha set_id como PK. Quando seu código python faz a inserção, ele pega o id que foi inserido graças à cláusula "INSERT ... RETURNING". Você pode então usar esse valor para as 6 linhas para inserir em "measurement_metadata".

O mesmo se aplica às outras tabelas, toda vez que você tiver um FK: insira na tabela pai, pegue o id gerado, use-o para inserir na tabela filho.

Lembre-se de envolver tudo em uma transação. Isso garante que ele funcionará ou falhará (nenhum dado incompleto poderá ser inserido) e é muito mais rápido, pois evita sobrecarga de COMMIT por linha.