What are your thoughts on this? I'm working on integrating some new data that's in a tab-delimited text format, and all of the decimal columns are kept as single integers; in order to determine the decimal amount you need to multiply the number by .01. It does this for things like percentages, weight and pricing information. For example, an item's price is expressed as 3259 in the data files, and when I want to display it I would need to multiply it in order to get the "real" amount of 32.59.
你对此有何看法?我正在努力集成一些以制表符分隔的文本格式的新数据,并且所有的十进制列都保持为单个整数;为了确定你需要将数字乘以.01的小数。它可以用于百分比,重量和价格信息。例如,项目的价格在数据文件中表示为3259,当我想显示它时,我需要将其相乘以获得32.59的“实际”金额。
Do you think this is a good or bad idea? Should I be keeping my data structure identical to the one provided by the vendor, or should I make the database columns true decimals and use SSIS or some sort of ETL process to automatically multiply the integer columns into their decimal equivalent? At this point I haven't decided if I am going to use an ORM or Stored Procedures or what to retrieve the data, so I'm trying to think long term and decide which approach to use. I could also easily just handle this in code from a DTO or similar, something along the lines of:
你认为这是一个好主意还是坏主意?我应该保持我的数据结构与供应商提供的数据结构相同,还是应该使数据库列为真小数,并使用SSIS或某种ETL过程自动将整数列乘以其十进制等值?此时我还没有决定是否要使用ORM或存储过程或者检索数据的内容,因此我尝试长期思考并决定使用哪种方法。我也可以轻松地在DTO或类似的代码中处理这个问题,类似于:
public class Product
{
// ...
private int _price;
public decimal Price
{
get
{
return (this._price * .01);
}
set
{
this._price = (value / .01);
}
}
}
But that seems like extra and unnecessary work on the part of a class. How would you approach this, keeping in mind that the data is provided in the integer format by a vendor that you will regularly need to get updates from.
但这似乎是课堂上额外和不必要的工作。您将如何处理此问题,请记住,数据是由供应商以整数格式提供的,您将经常需要从中获取更新。
4 个解决方案
#1
"Do you think this is a good or bad idea?"
“你认为这是一个好主意还是坏主意?”
Bad.
"Should I be keeping my data structure identical to the one provided by the vendor?"
“我应该保持我的数据结构与供应商提供的数据结构相同吗?”
No.
"Should I make the database columns true decimals?"
“我应该将数据库列设为真正的小数吗?”
Yes.
It's so much simpler to do what's right. Currently, the data is transmitted with no "." to separate the whole numbers from the decimals; that doesn't any real significance.
做正确的事情要简单得多。目前,数据没有“。”传输。将整数与小数分开;这没有任何实际意义。
The data is decimal. Decimal math works. Use the decimal math provided by your language and database. Don't invent your own version of Decimal arithmetic.
数据是十进制的。十进制数学有效。使用您的语言和数据库提供的十进制数学运算。不要发明自己的十进制算术版本。
#2
Personally I would much prefer to have the data stored correctly in my database and just do a simple conversion every time an update comes in.
就个人而言,我更希望在我的数据库中正确存储数据,并且每次更新时都进行简单的转换。
#3
Pedantically: they aren't kept as ints either. They are strings that require parsing.
迂腐:他们也没有保持整洁。它们是需要解析的字符串。
Philisophically: you have information in the file and you should write data into the database. That means transforming the information in any ways necessary to make it meaningful/useful. If you don't do this transform up front, then you'll be doomed to repeat the transform across all consumers of the database.
Philisophically:你在文件中有信息,你应该将数据写入数据库。这意味着以任何必要的方式转换信息,使其变得有意义/有用。如果你不事先做好转换,那么你将注定要在数据库的所有消费者中重复转换。
There are some scenarios where you aren't allowed to transform the data, such as being able to answer the question: "What was in the file?". Those scenarios would require the data to be written as string - if the parse failed, you wouldn't have an accurate representation of the file.
在某些情况下,您不允许转换数据,例如能够回答问题:“文件中有什么?”。这些场景需要将数据写为字符串 - 如果解析失败,您将无法准确表示该文件。
#4
In my mind the most important facet of using Decimal over Int in this scenario is maintainability.
在我看来,在这种情况下使用Decimal over Int最重要的方面是可维护性。
Data stored in the tables should be clearly meaningful without need for arbitrary manipulation. If manipulation is required is should be clearly evident that it is (such as from the field name).
存储在表中的数据应该清晰有意义,而不需要任意操作。如果需要操作,则应该清楚地表明它是(例如来自字段名称)。
I recently dealt with data where days of the week were stored as values 2-8. You can not imagine the fall out this caused (testing didn't show the problem for a variety of reasons, but live use did cause political explosions).
我最近处理了数据,其中一周中的几天存储为值2-8。你无法想象这引起的失败(测试没有出现各种原因的问题,但实际使用确实导致了政治爆炸)。
If you do ever run in to such a situation, I would be absolutely certain to ensure data can not be written to or read from the table without use of stored procedures or views. This enables you to ensure the necessary manipulation is both enforced and documented. If you don't have both of these, some poor sod who follows you in the future will curse your very name.
如果您遇到过这样的情况,我绝对肯定会确保在不使用存储过程或视图的情况下无法将数据写入表中或从表中读取数据。这使您可以确保强制执行和记录必要的操作。如果你没有这两种,那么将来会跟你一起的一些可怜的草皮会诅咒你的名字。
#1
"Do you think this is a good or bad idea?"
“你认为这是一个好主意还是坏主意?”
Bad.
"Should I be keeping my data structure identical to the one provided by the vendor?"
“我应该保持我的数据结构与供应商提供的数据结构相同吗?”
No.
"Should I make the database columns true decimals?"
“我应该将数据库列设为真正的小数吗?”
Yes.
It's so much simpler to do what's right. Currently, the data is transmitted with no "." to separate the whole numbers from the decimals; that doesn't any real significance.
做正确的事情要简单得多。目前,数据没有“。”传输。将整数与小数分开;这没有任何实际意义。
The data is decimal. Decimal math works. Use the decimal math provided by your language and database. Don't invent your own version of Decimal arithmetic.
数据是十进制的。十进制数学有效。使用您的语言和数据库提供的十进制数学运算。不要发明自己的十进制算术版本。
#2
Personally I would much prefer to have the data stored correctly in my database and just do a simple conversion every time an update comes in.
就个人而言,我更希望在我的数据库中正确存储数据,并且每次更新时都进行简单的转换。
#3
Pedantically: they aren't kept as ints either. They are strings that require parsing.
迂腐:他们也没有保持整洁。它们是需要解析的字符串。
Philisophically: you have information in the file and you should write data into the database. That means transforming the information in any ways necessary to make it meaningful/useful. If you don't do this transform up front, then you'll be doomed to repeat the transform across all consumers of the database.
Philisophically:你在文件中有信息,你应该将数据写入数据库。这意味着以任何必要的方式转换信息,使其变得有意义/有用。如果你不事先做好转换,那么你将注定要在数据库的所有消费者中重复转换。
There are some scenarios where you aren't allowed to transform the data, such as being able to answer the question: "What was in the file?". Those scenarios would require the data to be written as string - if the parse failed, you wouldn't have an accurate representation of the file.
在某些情况下,您不允许转换数据,例如能够回答问题:“文件中有什么?”。这些场景需要将数据写为字符串 - 如果解析失败,您将无法准确表示该文件。
#4
In my mind the most important facet of using Decimal over Int in this scenario is maintainability.
在我看来,在这种情况下使用Decimal over Int最重要的方面是可维护性。
Data stored in the tables should be clearly meaningful without need for arbitrary manipulation. If manipulation is required is should be clearly evident that it is (such as from the field name).
存储在表中的数据应该清晰有意义,而不需要任意操作。如果需要操作,则应该清楚地表明它是(例如来自字段名称)。
I recently dealt with data where days of the week were stored as values 2-8. You can not imagine the fall out this caused (testing didn't show the problem for a variety of reasons, but live use did cause political explosions).
我最近处理了数据,其中一周中的几天存储为值2-8。你无法想象这引起的失败(测试没有出现各种原因的问题,但实际使用确实导致了政治爆炸)。
If you do ever run in to such a situation, I would be absolutely certain to ensure data can not be written to or read from the table without use of stored procedures or views. This enables you to ensure the necessary manipulation is both enforced and documented. If you don't have both of these, some poor sod who follows you in the future will curse your very name.
如果您遇到过这样的情况,我绝对肯定会确保在不使用存储过程或视图的情况下无法将数据写入表中或从表中读取数据。这使您可以确保强制执行和记录必要的操作。如果你没有这两种,那么将来会跟你一起的一些可怜的草皮会诅咒你的名字。