不同数据类型之间的连接
在特定场景中，我有一个包含 MachineNumber (Varchar) 及其 MachineID (Int) 的查找表。在我的事务表中，我使用查找表的 MachineID 来引用机器编号。
要求：能否将事务表中的 MachineID 字段类型设置为 Varchar，同时在查找表中保留其类型为 Int？原因，对于某些机器，MachinNumber 未存储在查找表中，我需要将用户提供的实际 MachineNumber 存储在事务表本身中，并且由于机器编号是字母数字，我想从 Int 更改此引用字段的类型到瓦尔查尔。
简而言之，事务表中的 MachineID 字段应包含 MachineID（从查找表引用）和实际机器编号（由用户提供并直接存储在此处）
问题：为这些引用字段设置不同的类型是一个好习惯吗？或者这是一种常见的做法，对这两个表之间的Join查询有何影响？
谢谢， 阿林德
In a particular scenario I have a lookup table with MachineNumber (Varchar) and its MachineID (Int). In my transaction table I refer to the Machine number using lookup table's MachineID.
Requirement: Can I have the MachineID field type set as Varchar in the transaction table whereas retaining its type as Int in the lookup table? Reason, for some Machines the MachinNumber is not stored in the Lookup table and I need to store the actual MachineNumber provided by the user in the Transaction table itself and as the Machine number is alphanumeric I want to change the type of this referenced field from Int to Varchar.
In a nutshell in the transaction table the MachineID field should contain both MachineID (referenced from the lookup table) and actual Machine Number (provided by the user and stored directly here)
Question: Is it a good practice to have different types for these referenced fields or is it a common practice, what will be the effect on the queries with Join between these two tables?
Thanks,
Alind
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
作为一般规则，首选使用自然数据作为密钥，当然，如果您保证它在所有情况下都存在且唯一。
在你的情况下，如果你可以使用实际机器ID（varchar），那么就这样做，并将其作为你的查找等的关键。
我要补充一点，我曾经在一家拥有EFTPOS的公司工作过与我们的服务器进行交易的终端。我们决定使用方便的唯一 EFTPOS 终端 ID，但当它们坏了并被银行的维修人员更换时，我们遇到了问题。新终端有不同的 ID，但使用的是“相同”的终端。当终端被修复并放回另一个客户位置的现场时，我们的问题变得更加复杂 - 具有相同的原始终端 ID！这最初给我们带来了无穷无尽的麻烦。我们最终使用了一个人工 ID，并将当前物理终端 ID 作为一个属性。
As a general rule, using natural data as the key is preferred, if of course you it is guaranteed to be present and unique in all cases.
In your case, if you can use the actual machine id (varchar), then do so, and make that the key for your lookups etc.
I will add that I once worked for a company that had EFTPOS terminals transacting with our server. We decided to use the convenient unique EFTPOS terminal ID, but we had problem when they broke down and were swapped out by a repair guy from the bank. The new terminal had a different ID, but to use was "the same" terminal. Our problems were compounded when the terminal was repaired and put back into the field at another client location - with the same original terminal id! This initially caused us no end of hassle. We ended up using an artificial id, and sotred the current physical terminal id as an attribute.