Melhor maneira de armazenar unidades no banco de dados

Como você mencionou centenas de colunas, eu consideraria um projeto EAV . Embora Joe Celko avise contra isso , acho que pode ser aplicável ao seu caso de uso. Parece que todos os seus "valores" são números, então você evitaria os problemas de elenco que Joe descreve e a necessidade de transformar cada "valor" em uma string. Funcionará ainda melhor se todos os valores forem números inteiros, mas também pode funcionar se alguns forem decimais. Dadas as unidades de medida, você poderia dar um passo adiante e implementar um modelo de estilo "modelo de dados universal" baseado neste artigo de David Hay e também descrito em seu livro Padrões de modelo de dados: convenções de pensamento. Este modelo tem a vantagem adicional de configurar quais "quantidades" se aplicam a quais "coisas" se você precisar. Uma etapa adicional mostrada no livro na página 162 é uma tabela de conversão de unidades de medida que você pode usar para converter entre as diferentes unidades de medida. Aqui está um exemplo:

UOM Conversion              

UOM From    UOM To        Cal Step  Operator Factor Constant
Kilograms   Pounds        1         *        2.2
Celsius     Fahrenheit    1         *        1.8
Celsius     Fahrenheit    2         +               32

Isso diz que para converter de Kg para Lb o primeiro passo é multiplicar Kg por 2,2. Há também uma constante se uma conversão também deve incluir um valor constante e a capacidade de criar várias etapas. Portanto, ao converter, digamos, Celsius para Fahrenheit, você multiplica Celsius por 1,8 e depois adiciona 32. A chave seria de UDM, para UDM e a Etapa de Cálculo.

Esse é o meu valor de 2 centavos. Espero que essas referências lhe dêem um bom material para reflexão, caso você tenha a chance de reiniciar o design atual.

Todo o trabalho.

Observe que, no segundo caso, você não pode adicionar maçãs e laranjas e, portanto, os dados são excepcionalmente fáceis de serem interpretados incorretamente.

Observe também que as conversões não podem ser muito seguras e são suscetíveis a erros de arredondamento, estouros, etc.

Além disso, existem questões físicas como a gravidade específica e a temperatura. Converter 20 galões de água em libras exigiria que você conhecesse a densidade da água. Mas a densidade da água muda com a temperatura, então você pode precisar saber a densidade contemporânea à medição ou a temperatura da mesma forma e usar um fator de correção de volume.

No caso das propriedades estendidas, isso é bom apenas para documentação - um bom nome de coluna é melhor para documentação. O problema com a coluna implícita como estando em uma unidade fixa pelo nome é que você acaba se colocando em um canto quando muda as unidades de medida - o novo cliente quer óleo em barris e não galões - e isso seria bom, já que seus dados estão em seu próprio banco de dados, mas o nome da coluna agora é enganoso.

Outra opção é armazenar versões canônicas em unidades fixas (ou seja, sempre quilogramas e metros), além das medidas originais variáveis. As operações agregadas nas unidades fixas devem funcionar bem (exceto se você não adicionar temperaturas, por exemplo), mas não perde a medição original.

Uma solução simples que funcionou bem para mim no passado é armazenar todos os seus dados nas unidades 'base'. Por exemplo, sua unidade básica para comprimentos pode ser milímetros e sua unidade básica para pesos pode ser quilogramas. Esta solução pode resultar na necessidade de converter alguns de seus dados existentes na unidade base, caso ainda não o seja.

Depois de ter todos os dados nas unidades de base padrão, não há necessidade de armazenar a unidade no próprio banco de dados, pois agora é uma suposição de todo o sistema. As unidades exibidas necessárias para cada tipo de unidade (por exemplo, se deve exibir mm, polegadas, cm, m para comprimento) torna-se um problema de domínio do aplicativo/cliente, que pode ser salvo no armazenamento local.

As tabelas de conversão de unidades para conversão entre as várias unidades suportadas podem ser codificadas em seu aplicativo, uma vez que as novas unidades de medida raramente mudam.

NB, uma solução relacionada a outro problema é que, ao armazenar carimbos de data/hora em um banco de dados, armazene-os sempre na unidade 'base' - UTC .

Outra sessão de perguntas e respostas relacionada ao tópico ...

• https://stackoverflow.com/questions/12977021/best-practice-for-storing-weights-in-a-sql-database

• Isso contém algumas boas informações sobre por que usar um tipo de coluna de ponto flutuante é a melhor maneira de armazenar medições do mundo real.

Como qualquer unidade pode ser convertida em outra unidade do mesmo tipo Com a fórmula:

y = ((x + xOffset) * multiplicand / denominator) + yOffset

Eu criaria uma tabela que contém os tipos de unidade mais esses 4 valores.

From Unit     To Unit      Unit Type    From Offset    Multiplicand    Denominator    To Offset
'milligrams'  'grams'      'mass'       0              1               1000           0
'grams'      'kilograms'   'mass'       0              1               1000           0
'grams'      'ounces'      'mass'       0              100000          2835           0
'ounces'     'pound'       'mass'       0              1               16             0

Depois de adicionar todas as medidas que você provavelmente converterá em ambos os lados da lista, execute uma consulta onde você insere a operação inversa simplesmente negando os deslocamentos e trocando o multiplicando e o denominador e a unidade para e da unidade.

Para adicionar conversão entre todos os tipos, uma junção cruzada com alguns filtros pode inserir as conversões restantes.

Depois de ler a resposta de @Todd Everett, uma solução me ocorreu, então vou em frente e respondo à minha própria pergunta. O que acho que vou fazer é criar uma ColumnUnitstabela separada, com quatro colunas: Schema, Table, Column, UnitsID(onde UnitsID é FK para uma UnitsOfMeasuretabela separada), mapeando assim qualquer coluna dada para sua Unidade de Medida associada. Obviamente, a maior desvantagem dessa ideia é que os desenvolvedores teriam que se lembrar de editar essa tabela sempre que renomeassem uma coluna ou tabela [ talvez usar um gatilho DDL ? ], caso contrário, o sistema irá quebrar. Mas supondo que tais renomeações sejam raras e a loja de desenvolvimento pequena (apenas uma pessoa, no meu caso), essa arquitetura deve ser viável. A vantagem é que nenhuma alteração invasiva precisa ser feita no banco de dados atual e só preciso armazenar o valor uma vez para cada coluna, em vez de uma vez por linha, como exigiria minha segunda opção em minha postagem original.