My workplace has gotten the go ahead to (finally) modernize our application. One of the major pushes is to tear apart and redo our database. There has always been something within the database that has baffled me though: we have various tables in the code for populating small, basically unchanging dropdown menus (hair color, eye color, gender, etc.) and the integer primary key to the element in each table is used as a foreign key in the main (fairly large) table. This never made sense to me since it seems there may be some overhead there and our main goal is high performance and maintainability. My question is then twofold:
我的工作场所已经开始(最终)使我们的应用程序现代化。其中一个主要推动力是撕裂和重做我们的数据库。数据库中总有一些东西让我感到困惑:我们在代码中有各种表格,用于填充小的,基本不变的下拉菜单(头发颜色,眼睛颜色,性别等)和元素中的整数主键。每个表都用作主(相当大)表中的外键。这从来没有对我有意义,因为它似乎有一些开销,我们的主要目标是高性能和可维护性。我的问题是双重的:
-
As far as the size of the data getting stored, is there a significant difference in storage size and/or performance for using an integer FK for these tables' relation vs. storing a varchar that will probably top out at length 7 or 8 maybe?
就存储数据的大小而言,对于这些表的关系使用整数FK存储大小和/或性能是否存在显着差异,而存储可能在7或8长度可能最高的varchar?
-
Is there a significant performance difference between populating dropdowns from a database table and storing their PK value as an FK in the main table vs. populating the dropdown in our code and simply saving the string value?
在数据库表中填充下拉列表并将其PK值作为FK存储在主表中与填充代码中的下拉列表并简单地保存字符串值之间是否存在显着的性能差异?
When all is said and done our application will be written in C# with .NET 4.0 using MVC4 and the entity framework. Our database will be an MS SQL Server 2012 database. Thanks for the help, sorry if this is a repeat. I couldn't figure out what to search on.
完成所有操作后,我们的应用程序将使用MVC4和实体框架用.NET 4.0编写。我们的数据库将是MS SQL Server 2012数据库。感谢您的帮助,对不起,如果这是重复。我无法弄清楚要搜索什么。
2 个解决方案
#1
-1
Sounds like OOO (object oriented overkill). The solution is to abstract to a reasonable level. In this case, if you are "baffled" and it "never made sense", it's probably unnecessary. There is no right or wrong way to do it, but in my opinion, you won't have any problem doing what you described.
听起来像OOO(面向对象的矫枉过正)。解决方案是抽象到合理的水平。在这种情况下,如果你“感到困惑”而且“从来没有意义”,那可能就没必要了。没有正确或错误的方法,但在我看来,做你所描述的事情你不会有任何问题。
-
Of course varchar takes more space than a FK. Calculate the number of records you're planning on storing, and you'll get the size difference. Is is negligible? Probably.
当然varchar占用的空间比FK多。计算您计划存储的记录数量,您将获得尺寸差异。可以忽略不计?大概。
-
If anything, that should be faster than populating from a database.
如果有的话,那应该比从数据库填充更快。
#2
-1
1) CPU only talk in binary, the closest data in our world is number. the string/varchar you using, will be translate/map to number when the cpu is crunching them.
1)CPU只以二进制通话,我们世界上最接近的数据是数字。您使用的字符串/ varchar将在cpu处理它们时将其转换/映射到数字。
so theoritically+logically, using number should be best option
所以理论上和逻辑上,使用数字应该是最好的选择
as for whether the performance gain will be "significant", this only is more tangible when you testing on a huge volume of data, you have to measure them.
至于性能增益是否“重要”,当你测试大量数据时,这只是更有形,你必须测量它们。
in db general/best practice, number is recommended as key.
在db general / best practice中,建议使用数字作为密钥。
but imo, varchar should be no difference, though you have to be aware the case-sensitivity(collateral) may break your design
但imo,varchar应该没有区别,但你必须要注意区分大小写(抵押品)可能会破坏你的设计
so using number will save you alot of unforsee/future/integration problems.
所以使用数字将为您节省很多不可预见/未来/集成问题。
2) if you are into performance, denormalization is the generally one of the approach. but you should only go into this if you really need the performance and have no better alternative.
2)如果你进入表演,非规范化通常是一种方法。但是如果你真的需要性能并没有更好的选择,你应该只进入这个。
you need to understand why/should you adopt normalization, which generally yield more practical benefits than denormalization.
你需要理解为什么/你应该采用归一化,这通常会产生比非规范化更实际的好处。
#1
-1
Sounds like OOO (object oriented overkill). The solution is to abstract to a reasonable level. In this case, if you are "baffled" and it "never made sense", it's probably unnecessary. There is no right or wrong way to do it, but in my opinion, you won't have any problem doing what you described.
听起来像OOO(面向对象的矫枉过正)。解决方案是抽象到合理的水平。在这种情况下,如果你“感到困惑”而且“从来没有意义”,那可能就没必要了。没有正确或错误的方法,但在我看来,做你所描述的事情你不会有任何问题。
-
Of course varchar takes more space than a FK. Calculate the number of records you're planning on storing, and you'll get the size difference. Is is negligible? Probably.
当然varchar占用的空间比FK多。计算您计划存储的记录数量,您将获得尺寸差异。可以忽略不计?大概。
-
If anything, that should be faster than populating from a database.
如果有的话,那应该比从数据库填充更快。
#2
-1
1) CPU only talk in binary, the closest data in our world is number. the string/varchar you using, will be translate/map to number when the cpu is crunching them.
1)CPU只以二进制通话,我们世界上最接近的数据是数字。您使用的字符串/ varchar将在cpu处理它们时将其转换/映射到数字。
so theoritically+logically, using number should be best option
所以理论上和逻辑上,使用数字应该是最好的选择
as for whether the performance gain will be "significant", this only is more tangible when you testing on a huge volume of data, you have to measure them.
至于性能增益是否“重要”,当你测试大量数据时,这只是更有形,你必须测量它们。
in db general/best practice, number is recommended as key.
在db general / best practice中,建议使用数字作为密钥。
but imo, varchar should be no difference, though you have to be aware the case-sensitivity(collateral) may break your design
但imo,varchar应该没有区别,但你必须要注意区分大小写(抵押品)可能会破坏你的设计
so using number will save you alot of unforsee/future/integration problems.
所以使用数字将为您节省很多不可预见/未来/集成问题。
2) if you are into performance, denormalization is the generally one of the approach. but you should only go into this if you really need the performance and have no better alternative.
2)如果你进入表演,非规范化通常是一种方法。但是如果你真的需要性能并没有更好的选择,你应该只进入这个。
you need to understand why/should you adopt normalization, which generally yield more practical benefits than denormalization.
你需要理解为什么/你应该采用归一化,这通常会产生比非规范化更实际的好处。