Pediram-me para criar algo que rastreie o custo diário a ser cobrado nas contas e estou tentando descobrir um esquema de tabela de banco de dados que suporte isso.
Aqui está o que eu sei
- Empresa tem mais de 2,5 milhões de contas
- Destes, trabalham atualmente uma média de 200.000 por mês (isso muda com os níveis de pessoal, que atualmente são baixos)
- Eles têm 13 tipos de custo diferentes que gostariam de rastrear e avisaram que podem adicionar mais no futuro
- Eles querem que os custos sejam rastreados diariamente
- Os custos não são divididos em todo o inventário. Eles são divididos entre o número de contas trabalhadas por mês (200.000) ou os usuários podem inserir identificadores de conta para aplicar um custo a um grupo de contas ou podem simplesmente especificar a quais contas aplicar o custo.
Meu primeiro pensamento foi um banco de dados normalizado:
Id da conta Encontro CostTypeId Quantia
Meu problema com isso é, faça as contas. Esta mesa vai ficar enorme rapidamente. Supondo que todos os 13 tipos de custo sejam aplicados a todas as contas trabalhadas no mês atual, ou seja 200k * 13 * N days in month, cerca de 75 a 80 milhões de registros por mês ou quase um bilhão de registros por ano.
Meu segundo pensamento foi desnormalizar um pouco
Id da conta Encontro Custo total CostType1 CostType2 CostType3 CostType4 CostType5 CostType6 CostType7 CostType8 CostType9 CostType10 CostType11 CostType12 CostType13
Esse método é mais desnormalizado e pode gerar até 6 milhões de registros por mês ( 200k * N days in month), ou cerca de 72 milhões por ano. É muito menor do que o primeiro método, porém se a empresa decidir por um novo tipo de custo no futuro, outra coluna de banco de dados precisará ser adicionada.
Dos dois métodos, qual você prefere? Por quê? Existe outra alternativa que você possa pensar que lidaria melhor com isso?
Estou mais interessado em relatar o desempenho, tanto relatórios resumidos quanto detalhados. O trabalho que distribuirá os custos pelas contas será executado todas as noites, quando não houver ninguém por perto. Uma preocupação secundária é o tamanho do banco de dados. O banco de dados existente já tem quase 300 GB e acredito que o espaço em disco esteja em torno de 500 GB.
O banco de dados é SQL Server 2005
Um bilhão de registros por ano não é muito.
Com particionamento (talvez por tipo de custo) e arquivamento, é gerenciável.
O número de itens de dados a serem armazenados ainda é 200k * 13 * N. Como colunas, você obterá menos linhas por página e ocupará mais espaço do que como linhas. Você pode ganhar se "CostType1" não for um tipo de dados de comprimento fixo, mas for marginal.
"BEIJO" como dizem
Embora seu design possa certamente fazer a diferença durante a noite ou o dia, neste caso, eu me concentraria mais nos índices, incluindo a cobertura de índices conforme necessário. Também examinaria algumas das ferramentas que o SQL Server oferece para lidar com tabelas muito grandes, como particionamento de tabelas.
Pense desta forma, mesmo que haja 80 bilhões de registros na tabela, com indexação adequada, aqueles nos quais você está realmente interessado em um determinado ponto serão agrupados fisicamente no disco. Devido à maneira como os dados são organizados no servidor SQL, os dados divididos por limites de índice também podem estar em outra tabela porque não é necessário ler a tabela inteira para obter o que é necessário.
Se você também optar por particionar a tabela, poderá melhorar o tempo de acesso e o tempo de inserção.
eu normalizaria. Fizemos contabilidade de custos para a lucratividade da conta do cliente em um banco e geramos mais de 250 milhões de linhas de custos individuais usando centenas de drivers alocados por centro de custo ou por razão geral ou por várias outras técnicas em milhões de contas a cada mês.
Por exemplo, o custo total de manutenção de caixas eletrônicos foi dividido entre as contas que usaram caixas eletrônicos com base na quantidade relativa de uso. Portanto, se US$ 1 milhão foi gasto atendendo caixas eletrônicos e apenas 5 clientes o usaram uma vez cada e um cliente o usou 5 vezes, então esse cliente custou ao banco US$ 0,5 milhão e os outros clientes custaram ao banco US$ 0,1 milhão cada. Outros drivers podem ser muito mais complexos.
No final das contas, você provavelmente descobrirá que é escasso - certas contas não recebem custos de certas fontes / drivers - e algumas contas não recebem nada. Em um modelo normalizado, essas linhas não existem. No modelo desnormalizado, a linha existe, com algumas colunas vazias. Além disso, em um modelo esparso normalizado, você deve ver o desempenho melhorar, porque a existência de uma linha é normalmente mais rápida de verificar (com índice de cobertura em CostType) do que verificar todas as linhas com não NULL em um "balde" específico (mesmo com índices em cada coluna de quantidade - que você pode ver começa a ficar muito desperdício).
Independentemente do benefício de desempenho, eu definitivamente seria a favor da opção 1. A opção 2 seria roubar Peter para pagar Paul, na minha opinião.
Eu escolheria a opção 1 e, se a velocidade do relatório se tornasse um problema no futuro, também adicionaria a tabela 2 e a preencheria em um banco de dados de relatórios em algum tipo de processo automatizado durante a noite / fora do horário de pico.
Você também pode considerar o acúmulo da estrutura da tabela diária 2 em mais acúmulos semanais, mensais, trimestrais e anuais, se necessário.
Mas, como eu disse, também escolheria armazenar os dados 'brutos' na forma adequada (normalizada).
Considerando os volumes que você mencionou, eu optaria pela segunda opção, mas sem o TotalCost. Você poderia dizer que ainda está normalizado.
Editar: como alternativa, e dependendo de seus requisitos e do tamanho do AccountId, você também pode considerar o seguinte:
Com esse design, você ainda pode adicionar um TotalCost desnormalizado à primeira tabela e recalculá-lo todas as noites, permitindo executar alguns relatórios apenas na primeira tabela.
na verdade, você deve dividir a primeira tabela em duas tabelas para poder usar uma subconsulta e selecionar a segunda linha como uma coluna ou várias colunas. é mais flexível assim e com isso você consegue um resultado como o segundo com mais facilidade.