Is creating user defined types instead of using existing types best practice? In my previews work place all basic types were predefined, is it best practice? what advantages and what disadvantage it has.
Thanks a lot!
是创建用户定义的类型而不是使用现有类型的最佳实践?在我的预览工作场所,所有基本类型都是预定义的,这是最佳实践吗?它有什么优点和缺点。非常感谢!
3 个解决方案
#1
In MS SQL Server - the idea is great. However, there's a BIG but to them: you can't change them once they're in use, since there's no ALTER TYPE .... statement. This can be a major hassle.
在MS SQL Server中 - 这个想法很棒。然而,对他们来说有一个很大的问题:一旦他们使用你就无法改变它们,因为没有ALTER TYPE ....声明。这可能是一个很大的麻烦。
Consider this: you have a bookstore app which has a user-defined type ISBN = VARCHAR(10) - works like a charm. Now the international committee decided to increate the ISBN field to 13 characters - unfortunately, there's no ALTER TYPE ISBNType..... so your only choice is to basically drop all the columns of that type in all of your database, then re-create the type in its new form, and re-create all those columns again.
考虑一下:你有一个书店应用程序,其用户定义类型ISBN = VARCHAR(10) - 就像一个魅力。现在国际委员会决定将ISBN字段增加到13个字符 - 遗憾的是,没有ALTER TYPE ISBNType .....所以你唯一的选择是基本上删除所有数据库中的所有列,然后重新创建新表单中的类型,并再次重新创建所有这些列。
The idea is great and I would love to use it - but in its current state, it's next to unusable, in my opinion, which is unfortunate.
这个想法很棒,我很乐意使用它 - 但在目前的状态下,在我看来,这是不可用的,这是不幸的。
Marc
#2
A bit late, but.. my 2 cts.
有点晚了,但是......我的2个。
The user defined types fulfill the role of a domain in the conceptual and logical models, so if you want to implement your logical datamodel in the physical database, the use of user defined types would be the closest approximation. This will provide benefits such as using a graphical model that can be transferred to an implementation automatically, better than it could be done without using UDTs.
用户定义的类型在概念和逻辑模型中履行域的角色,因此如果要在物理数据库中实现逻辑数据模型,则使用用户定义的类型将是最接近的近似值。这将提供诸如使用可以自动传输到实现的图形模型的好处,比不使用UDT时更好。
Using domains makes it possible to detect attempts to store a postal code in an SSN field and have them caught by the database at run time (or even compile time). This will enable you to enforce company wide business rules where they should be enforced: at the point where the data hits the storage. Anywhere else is a courtesy to the user, but this is where they absolutely HAVE to be enforced to make sure noone goes around them.
使用域可以检测在SSN字段中存储邮政编码的尝试,并在运行时(甚至编译时)让它们被数据库捕获。这将使您能够在应该强制实施的公司范围内实施业务规则:在数据到达存储的位置。其他任何地方都是对用户的礼貌,但这是他们绝对必须执行的地方,以确保没有人绕过他们。
Also, UDTs as defined in ANSI SQL:1999 encapsulate methods, and can be subtyped. That means that certain changes in business rules that deal with constraints can be changed in the database, without ever having to touch an implementation.
此外,ANSI SQL:1999中定义的UDT封装了方法,并且可以进行子类型化。这意味着可以在数据库中更改处理约束的业务规则中的某些更改,而无需触及实现。
However... as currently implemented UDTs leave a lot to be desired. I'm not even sure SQL Server implements the standard as described, even less sure about Oracle. And even if they do, the disadvantages of using UDTs will become obvious right away as you use BI tooling, ETL tooling or other rather simple-minded tools: they don't usually understand UDTs and even if they do, they won't be able to use them because the majority base themselves on basic types (int, char and date/time).
但是......因为目前实施的UDT还有很多不足之处。我甚至不确定SQL Server是否如所描述的那样实现标准,甚至不太确定Oracle。即使他们这样做,使用UD工具,ETL工具或其他相当简单的工具时,使用UDT的缺点也会立即变得明显:他们通常不了解UDT,即使他们这样做,他们也不会能够使用它们,因为大多数都基于基本类型(int,char和date / time)。
As for reporting tools, let's consider a UDT that has a combination of fields. Now, how would a database-agnostic ETL-tool be able to understand the type? It would have to be able to understand the structure in order to display it, or use it for calculations. And that for every supported database - a difficult task. And since noone uses UDTs, they won't do this. It's a vicious circle, but we still have to live with it.
至于报告工具,让我们考虑一个具有字段组合的UDT。现在,与数据库无关的ETL工具如何能够理解该类型?它必须能够理解结构才能显示它,或者用它来进行计算。对于每个支持的数据库而言 - 这是一项艰巨的任务由于没有人使用UDT,他们不会这样做。这是一个恶性循环,但我们仍然需要忍受它。
Apart from that, UDT support in most databases isn't all that hot either. Changing types can be quite hard. While the syntax should be the same everywhere, their administration is undefined in SQL:1999 and therefore different everywhere. Try changing the owner of a UDT in sql server for an example. That used to be pretty difficult - not sure about SQL Server 2016, but given that UDTs don't look like a focus area, I'm not expecting major improvements in that area.
除此之外,大多数数据库中的UDT支持也不是那么热门。改变类型可能非常困难。虽然语法应该在任何地方都相同,但它们的管理在SQL:1999中是未定义的,因此在任何地方都是不同的。例如,尝试在sql server中更改UDT的所有者。过去相当困难 - 不确定SQL Server 2016,但鉴于UDT看起来不像一个焦点区域,我不期望在该领域有重大改进。
Lastly, if you add really complex code to UDTs, you run into the old problem that you are programming in an environment that has a lack of good debugging options. That doesn't help either.
最后,如果向UDT添加非常复杂的代码,则会遇到在缺少良好调试选项的环境中编程的旧问题。这也无济于事。
So: if you have full control over your database and all interaction goes through your software, UDTs can be extremely helpful. If not, they can be a huge pain the neck. Which one is the case in your scenario, is something only you can tell.
因此:如果您完全控制数据库并且所有交互都通过您的软件,那么UDT可能非常有用。如果没有,他们可能是一个巨大的痛苦的脖子。您的场景中的情况就是这种情况,这是您可以告诉的内容。
#3
How cool are User Defined Data Types in MS SQL Server?
MS SQL Server中的用户定义数据类型有多酷?
Personally, I don't use them but I've seen them. One problem is that client code recognises the base type only, IIRC, for MS SQL Server at least. And portability/behaviours across versions.
就个人而言,我不使用它们,但我已经看过它们了。一个问题是客户端代码至少仅为MS SQL Server识别基本类型IIRC。跨版本的可移植性/行为。
#1
In MS SQL Server - the idea is great. However, there's a BIG but to them: you can't change them once they're in use, since there's no ALTER TYPE .... statement. This can be a major hassle.
在MS SQL Server中 - 这个想法很棒。然而,对他们来说有一个很大的问题:一旦他们使用你就无法改变它们,因为没有ALTER TYPE ....声明。这可能是一个很大的麻烦。
Consider this: you have a bookstore app which has a user-defined type ISBN = VARCHAR(10) - works like a charm. Now the international committee decided to increate the ISBN field to 13 characters - unfortunately, there's no ALTER TYPE ISBNType..... so your only choice is to basically drop all the columns of that type in all of your database, then re-create the type in its new form, and re-create all those columns again.
考虑一下:你有一个书店应用程序,其用户定义类型ISBN = VARCHAR(10) - 就像一个魅力。现在国际委员会决定将ISBN字段增加到13个字符 - 遗憾的是,没有ALTER TYPE ISBNType .....所以你唯一的选择是基本上删除所有数据库中的所有列,然后重新创建新表单中的类型,并再次重新创建所有这些列。
The idea is great and I would love to use it - but in its current state, it's next to unusable, in my opinion, which is unfortunate.
这个想法很棒,我很乐意使用它 - 但在目前的状态下,在我看来,这是不可用的,这是不幸的。
Marc
#2
A bit late, but.. my 2 cts.
有点晚了,但是......我的2个。
The user defined types fulfill the role of a domain in the conceptual and logical models, so if you want to implement your logical datamodel in the physical database, the use of user defined types would be the closest approximation. This will provide benefits such as using a graphical model that can be transferred to an implementation automatically, better than it could be done without using UDTs.
用户定义的类型在概念和逻辑模型中履行域的角色,因此如果要在物理数据库中实现逻辑数据模型,则使用用户定义的类型将是最接近的近似值。这将提供诸如使用可以自动传输到实现的图形模型的好处,比不使用UDT时更好。
Using domains makes it possible to detect attempts to store a postal code in an SSN field and have them caught by the database at run time (or even compile time). This will enable you to enforce company wide business rules where they should be enforced: at the point where the data hits the storage. Anywhere else is a courtesy to the user, but this is where they absolutely HAVE to be enforced to make sure noone goes around them.
使用域可以检测在SSN字段中存储邮政编码的尝试,并在运行时(甚至编译时)让它们被数据库捕获。这将使您能够在应该强制实施的公司范围内实施业务规则:在数据到达存储的位置。其他任何地方都是对用户的礼貌,但这是他们绝对必须执行的地方,以确保没有人绕过他们。
Also, UDTs as defined in ANSI SQL:1999 encapsulate methods, and can be subtyped. That means that certain changes in business rules that deal with constraints can be changed in the database, without ever having to touch an implementation.
此外,ANSI SQL:1999中定义的UDT封装了方法,并且可以进行子类型化。这意味着可以在数据库中更改处理约束的业务规则中的某些更改,而无需触及实现。
However... as currently implemented UDTs leave a lot to be desired. I'm not even sure SQL Server implements the standard as described, even less sure about Oracle. And even if they do, the disadvantages of using UDTs will become obvious right away as you use BI tooling, ETL tooling or other rather simple-minded tools: they don't usually understand UDTs and even if they do, they won't be able to use them because the majority base themselves on basic types (int, char and date/time).
但是......因为目前实施的UDT还有很多不足之处。我甚至不确定SQL Server是否如所描述的那样实现标准,甚至不太确定Oracle。即使他们这样做,使用UD工具,ETL工具或其他相当简单的工具时,使用UDT的缺点也会立即变得明显:他们通常不了解UDT,即使他们这样做,他们也不会能够使用它们,因为大多数都基于基本类型(int,char和date / time)。
As for reporting tools, let's consider a UDT that has a combination of fields. Now, how would a database-agnostic ETL-tool be able to understand the type? It would have to be able to understand the structure in order to display it, or use it for calculations. And that for every supported database - a difficult task. And since noone uses UDTs, they won't do this. It's a vicious circle, but we still have to live with it.
至于报告工具,让我们考虑一个具有字段组合的UDT。现在,与数据库无关的ETL工具如何能够理解该类型?它必须能够理解结构才能显示它,或者用它来进行计算。对于每个支持的数据库而言 - 这是一项艰巨的任务由于没有人使用UDT,他们不会这样做。这是一个恶性循环,但我们仍然需要忍受它。
Apart from that, UDT support in most databases isn't all that hot either. Changing types can be quite hard. While the syntax should be the same everywhere, their administration is undefined in SQL:1999 and therefore different everywhere. Try changing the owner of a UDT in sql server for an example. That used to be pretty difficult - not sure about SQL Server 2016, but given that UDTs don't look like a focus area, I'm not expecting major improvements in that area.
除此之外,大多数数据库中的UDT支持也不是那么热门。改变类型可能非常困难。虽然语法应该在任何地方都相同,但它们的管理在SQL:1999中是未定义的,因此在任何地方都是不同的。例如,尝试在sql server中更改UDT的所有者。过去相当困难 - 不确定SQL Server 2016,但鉴于UDT看起来不像一个焦点区域,我不期望在该领域有重大改进。
Lastly, if you add really complex code to UDTs, you run into the old problem that you are programming in an environment that has a lack of good debugging options. That doesn't help either.
最后,如果向UDT添加非常复杂的代码,则会遇到在缺少良好调试选项的环境中编程的旧问题。这也无济于事。
So: if you have full control over your database and all interaction goes through your software, UDTs can be extremely helpful. If not, they can be a huge pain the neck. Which one is the case in your scenario, is something only you can tell.
因此:如果您完全控制数据库并且所有交互都通过您的软件,那么UDT可能非常有用。如果没有,他们可能是一个巨大的痛苦的脖子。您的场景中的情况就是这种情况,这是您可以告诉的内容。
#3
How cool are User Defined Data Types in MS SQL Server?
MS SQL Server中的用户定义数据类型有多酷?
Personally, I don't use them but I've seen them. One problem is that client code recognises the base type only, IIRC, for MS SQL Server at least. And portability/behaviours across versions.
就个人而言,我不使用它们,但我已经看过它们了。一个问题是客户端代码至少仅为MS SQL Server识别基本类型IIRC。跨版本的可移植性/行为。